RE: How to exclude few tables while gathering a schema stats in oracle 9i
- From: "Juan Miranda" <j.miranda@xxxxxxxxxxxx>
- To: <p4cldba@xxxxxxxxx>, "'oracle-l'" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 24 Dec 2007 23:10:16 +0100
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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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
Other related posts:
