Is there any way to run dbms_stats at the schema level and omit a few HUGE partitioned tables.
What we have now is a script that analyzes all of the tables for this schema, this works fine BUT.. we are adding another 40 or so tables to this schema and now have to keep updating this script.
Essentially we would like to avoid this and just have the flexability to analyze the monster tables at other times.
I do not see any options online, a simple WHERE clause would work if it existed.
i dont think there is an exceptions kinda thing, but you can do a little loop to select the table name from user_tables with a where clause then pass the table name to dbms_stats
SQL*Plus: Release 9.2.0.5.0 - Production on Wed Jun 29 16:28:47 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
rm: cannot remove `analyze.tmp.sql': No such file or directory
select to_char(sysdate,'MM/DD/YY HH24:MI:SS') from dual;abname => 'A', degree =>
select to_char(sysdate,'MM/DD/YY HH24:MI:SS') from dual;abname => 'A1', degree =>
select to_char(sysdate,'MM/DD/YY HH24:MI:SS') from dual;abname => 'A2110', degree
select to_char(sysdate,'MM/DD/YY HH24:MI:SS') from dual;abname => 'CUST_INFO', de
select to_char(sysdate,'MM/DD/YY HH24:MI:SS') from dual;abname => 'EXCEPTIONS', d
select to_char(sysdate,'MM/DD/YY HH24:MI:SS') from dual;abname => 'MLOG$_MY_OBJEC
select to_char(sysdate,'MM/DD/YY HH24:MI:SS') from dual;abname => 'MY_OBJECTS', d
select to_char(sysdate,'MM/DD/YY HH24:MI:SS') from dual;abname => 'MY_OBJECTS_MV'
select to_char(sysdate,'MM/DD/YY HH24:MI:SS') from dual;abname => 'ST', degree =>
select to_char(sysdate,'MM/DD/YY HH24:MI:SS') from dual;abname => 'STATES', degre
select to_char(sysdate,'MM/DD/YY HH24:MI:SS') from dual;abname => 'T', degree =>
select to_char(sysdate,'MM/DD/YY HH24:MI:SS') from dual;abname => 'T1', degree =>
I just rewrote my entire statistics gathering process. Basically what I do in this case is I have a table of owner/tablenames that want to exclude from analyzing. I use dbms_stats.gather_database_stats(options=>'LIST STALE') to get a list of objects to analyze. I then bounce those against my table that stores the objects I want to exclude to decide if I need to analyze the table.