SQL to Create 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

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';

_____________________________________________________________________________________________________________________

0 comments:

Post a comment

 

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