Hi Connor and listers,
This was a little joke on Larry Elkins. He has been
kidding me for months about how he will have to stop
writing bad things about me/us if I joined this list,
so I decided to play a little joke on him when I
joined. Thought about not excluding the SYS schema in
that script (so that it would look like we were
analyzing the DD) but decided that that might be a
little obvious.
Hope I didn't ruffle too many feathers by this post.
This is definitely NOT a reccommended operation.
Also, thanks for the input, that looks like the best
way to do it.
jack
- Connor McDonald <hamcdc_at_yahoo.co.uk> wrote:
> 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
=== message truncated ===
Do You Yahoo!?
Yahoo! Shopping - Mother's Day is May 12th!
http://shopping.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).
Received on Wed May 08 2002 - 23:18:17 CDT