Script to Analyze All Schema Tables and Indexes in Oracle Database

_____________________________________________________________________________________________________________________

The Eucharistic Miracles of the World
This script to generate the script to analyze all the tables in the specified schema(s) 



Method 1. 
Using dbms_stats




SET PAGES 10000 
SET LINES 444
SET HEAD OFF 
SET FEED OFF
spool analyze_table.sql
select 'exec dbms_stats.gather_table_stats(ownname => '''||owner||''',tabname => '''||table_name ||''', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE, cascade => TRUE,method_opt => '' FOR ALL COLUMNS SIZE AUTO'', degree => 8);' 
from dba_tables 
where owner='TEST';
spool off
advertisements
 

Method 2. 
Analyze table command

SET PAGES 10000 
SET LINES 444
SET HEAD OFF 
SET FEED OFF
spool analyze_table.sql
select 'ANALYZE TABLE ' || owner || '.' || table_name ||
' ESTIMATE STATISTICS SAMPLE 10 PERCENT;'
from dba_tables
where  owner ='TEST';
spool off

Script to create analyze index script

SET PAGES 10000 
SET LINES 444
SET HEAD OFF 
SET FEED OFF
spool analyze_index.sql
select 'ANALYZE index ' || owner || '.' || index_name ||
' ESTIMATE STATISTICS SAMPLE 10 PERCENT;'
from dba_indexes
where  owner ='TEST';

_____________________________________________________________________________________________________________________

Website Stats

0 comments:

Post a Comment

Labels

Oracle (580) General (60) Unix (47) Script (41) Blog (23) OCI (3) SQL* Loader (3) Datapump (2) Software (1)
DBA Tips
 

acehints.com Copyright 2011-21 All Rights Reserved | Site Map | Contact | Disclaimer