RE: How to exclude few tables while gathering a schema stats in oracle 9i

Hope this helps.    

set heading off echo off feedback off pagesize 5000 linesize 300 trimspool on  

spool c:\analyze_all.sql  

prompt set feedback on

prompt set echo on

prompt set trimspool on

prompt spool c:\analyze_all.log  

prompt select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') from dual;;  

prompt prompt +++++++++++++ small tables (<=100.000 filas) COMPUTE

++++++++++++++++++;;

select 'execute dbms_stats.gather_table_stats (ownname=>''' || upper(owner) || ''', tabname=> ''' || upper(table_name) || ''', method_opt=> ''FOR ALL INDEXED COLUMNS SIZE 1'', degree=>1, cascade=> true);' from DBA_TABLES where upper(owner) not in ('SYSTEM','SYS') and table_name not in ('TABLE1','TABLE2') order by owner, table_name;  

# and NUM_ROWS<=100000 order by owner, table_name;
 

#prompt prompt +++++++++++++ big tables (>100.000) o SIN NUM_ROWS

++++++++++++++++++;;

#select 'execute dbms_stats.gather_table_stats (ownname=>''' || upper(owner)
|| ''', tabname=> ''' || upper(table_name) || ''',estimate_percent=>dbms_stats.auto_sample_size, method_opt=>''FOR ALL INDEXED COLUMNS SIZE 1'', degree=>2, cascade=> true);'

# from DBA_TABLES where upper(owner) not in ('SYSTEM','SYS') and
(NUM_ROWS>100000 or NUM_ROWS is null) order by owner, table_name;    

prompt select to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') from dual;;

prompt spool off

spool off  

• Execute the script.

@c:\analyze_all.sql      

De: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] En nombre de Prasad
Enviado el: lunes, 24 de diciembre de 2007 21:47 Para: oracle-l
Asunto: How to exclude few tables while gathering a schema stats in oracle 9i  

All,

I need to do a stats generation for a oracle 9i db schema everynight. but there are few very large tables which I want to exclude while gathering the schema stats.

Can someone please direct me how i can achieve this in Oracle 9i .

Thanks
_Prasad

--
http://www.freelists.org/webpage/oracle-l