why not
alter table XXX nomonitoring;
alter table XXX monitoring;
in the script instead of playing with mon_mod$
hth
connor
- Jack Silvey <jack_silvey_at_yahoo.com> wrote: >
Howdy all,
>
>
> We had some problems with dbms_stats, so we went
> back
> to good ol analyze until dbms_stats firms up a
> little.
> But, I sure liked that ALTER TABLE MONITORING and
> dba_tab_modifications, so I figured out a way to
> enable us to remove rows from dba_tab_modifications
> once the analyze is complete.
>
> (BTW, Larry Elkins is working with us here, and just
> LOVES the fact that we are hacking the DD in
> production. ;) )
>
>
> Here it is:
>
>
> --THIS CURSOR GETS THE NON-PARTITIONED TABLE NAMES
> THAT ARE ON DBA_TAB_MODIFCATIONS:
>
> cursor c_tab is
> select u.name table_owner, o.name table_name,m.obj#
> from sys.mon_mods$ m, sys.obj$ o, sys.tab$
> t,sys.user$
> u
> where o.obj# = m.obj#
> and o.obj# = t.obj#
> and o.owner# = u.user#
> and u.name not in
> ('SYS','SYSTEM','OUTLN','PERFSTAT');
>
>
>
> --THIS CURSOR GETS THE TABLE PARTITIONS THAT ARE ON
> DBA_TAB_MODIFCATIONS LIST:
>
> cursor c_part is
> select u.name table_owner, o.name table_name,
> o.subname partition_name,m.obj#
> from sys.mon_mods$ m, sys.obj$ o, sys.user$ u
> where o.obj# = m.obj# and o.owner# = u.user# and
> o.obj# in (select obj# from sys.tabpart$
> union select obj# from sys.tabsubpart$)
> and u.name not in
> ('SYS','SYSTEM','OUTLN','PERFSTAT');
>
>
>
> begin
> for v_tab in c_tab loop
>
>
> --DO THE ANALYZE HERE:
>
> v_tabname := v_tab.table_name;
> v_own := v_tab.table_owner;
>
> v_sql := 'analyze table
> '||'"'||v_tab.table_owner||'"'||
> '.'||'"'||v_tab.table_name||'"'||' estimate
> statistics
> sample 35 percent for table';
>
> execute immediate v_sql;
>
> --REMOVE THE TABLES FROM DBA_TAB_MODIFICATIONS HERE:
>
> delete sys.mon_mods$
> where obj# = v_tab.obj#;
>
> commit;
>
> end loop;
>
> end;
>
> begin
> for v_part in c_part loop
>
> v_tabname := v_part.table_name;
> v_own := v_part.table_owner;
> v_partname := v_part.partition_name;
>
> --DO THE ANALYZE HERE:
>
>
> v_sql := 'analyze table '||
>
>
'"'||v_part.table_owner||'"'||'.'||'"'||v_part.table_name||'"'||
> ' partition('||v_part.partition_name||') estimate
> statistics
> sample 35 percent for table';
>
> execute immediate v_sql;
>
>
> --REMOVE THE PARTITIONS FROM DBA_TAB_MODIFICATIONS
> HERE:
>
>
> delete sys.mon_mods$
> where obj# = v_part.obj#;
>
> commit;
>
> end loop;
> end;
>
>
> good luck,
>
>
> Jack Silvey
>
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Health - your guide to health and wellness
> http://health.yahoo.com
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: Jack Silvey
> INET: jack_silvey_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX:
> (858) 538-5051
> San Diego, California -- Public Internet
> access / Mailing Lists
>
> To REMOVE yourself from this mailing list, send an
> E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of
> 'ListGuru') and in
> the message BODY, include a line containing: UNSUB
> ORACLE-L
> (or the name of mailing list you want to be removed
> from). You may
> also send the HELP command for other information
> (like subscribing).
Connor McDonald
http://www.oracledba.co.uk
http://www.oaktable.net
"Some days you're the pigeon, some days you're the statue"
Do You Yahoo!?
Everything you'll ever need on one web page
from News and Sport to Email and Music Charts
http://uk.my.yahoo.com
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: =?iso-8859-1?q?Connor=20McDonald?=
INET: hamcdc_at_yahoo.co.uk
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
San Diego, California -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from). You may
also send the HELP command for other information (like subscribing).
Received on Wed May 08 2002 - 18:03:26 CDT
