Hi Lisa,
That'll teach you for using stuff written in 2002, especially when its based on a script written
in 2001 :-)
Seriously though, I'd take a (very) thorough look at the driving select statement. I put it
together like that because at the time I was at a client with tens of thousands of segments so
queries to DBA_SEGMENTS just took forever, so I delved into the underlying view definition to get
some speedup.
The DBA_SEGMENTS definition is one of those things that changes *very* frequently (as our good
friends at Oracle add IOT's, compressed table, partitioned domain objects etc etc etc etc etc).
The basic concepts within the script remain valid, but you might want to put together a more
"version-robust" query to DBA_SEGMENTS/TABLES/INDEXES etc.
hth
connor
- "Koivu, Lisa" <Lisa.Koivu_at_Cendant-TRG.com> wrote: > It's BEAUTIFUL! Best thing that's
happened all day!
> Thanks Jacques for your help. I'm so grateful!
> Lisa
>
> -----Original Message-----
> From: Jacques Kilchoer [mailto:Jacques.Kilchoer_at_quest.com]=20
> Sent: Wednesday, January 28, 2004 3:40 PM
> To: oracle-l_at_freelists.org
> Cc: Koivu, Lisa
> Subject: RE: Analyze script written by Connor McDonald
>
> > -----Original Message-----
> > From: oracle-l-bounce_at_freelists.org
> > [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Koivu, Lisa
> >=20
> > I ran it in debug mode also and it doesn't list any of the partitioned
> > tables within the schema I'm selecting.
>
> Whoops! My mistake, I read the output wrong.
> It looks like there are some outer joins missing (partitioned
> tables/indexes will not have a corresponding row in sys.seg$) and the
> decode for the PARTITIONED column should use the PROPERTY column from
> tab$ / ind$.
> Will this work?
> Replace the SELECT statement in analyze_db with this one:
>
> SELECT segment_TYPE,segment_NAME,owner,
> decode(segment_type, 'TABLE', decode(bitand(property, 32), 32,
> 'YES', 'NO'), 'INDEX', decode(bitand(property, 2), 2, 'YES', 'NO'))
> partitioned
> bulk collect into
> v_segment_type, v_segment_name, v_owner, v_partitioned
> from ( select u.name owner, o.name segment_name, o.subname,
> so.object_type segment_type, o.obj# object_id, so.property
> from sys.user$ u, sys.obj$ o, sys.ts$ ts, sys.seg$ s, sys.file$
> f,
> ( select 'TABLE' object_type, 2 object_type_id, 5
> segment_type_id, t.obj#, t.file#, t.block# , t.ts#, t.property
> from sys.tab$ t
> where bitand(t.property, 1024) =3D 0
> and bitand(t.property,8192) =3D 0
> union all
> select 'INDEX', 1, 6, i.obj#, i.file#, i.block#, i.ts#,
> i.property
> from sys.ind$ i
> where i.type# in (1, 2, 3, 4, 6, 7, 9) ) so
> where s.file# (+) =3D so.file#
> and s.block# (+) =3D so.block#
> and s.ts# (+) =3D so.ts#
> and s.ts# =3D ts.ts# (+)
> and o.obj# =3D so.obj#
> and o.owner# =3D u.user#
> and s.type# (+) =3D so.segment_type_id
> and o.type# =3D so.object_type_id
> and s.ts# =3D f.ts# (+)
> and s.file# =3D f.relfile# (+)
> and u.name not in ('SYS','SYSTEM'))
> where owner =3D nvl(upper(p_owner),owner)
> and segment_type =3D nvl(upper(p_segment_type),segment_type)
> and mod(object_id,p_int1) =3D p_int2;
>
>
>
>
> "The sender believes that this E-Mail and any attachments were free of =
> any virus, worm, Trojan horse, and/or malicious code when sent. This =
> message and its attachments could have been infected during =
> transmission. By reading the message and opening any attachments, the =
> recipient accepts full responsibility for taking proactive and remedial =
> action about viruses and other defects. The sender's business entity is =
> not liable for any loss or damage arising in any way from this message =
> or its attachments."
>
> ----------------------------------------------------------------
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> ----------------------------------------------------------------
> To unsubscribe send email to: oracle-l-request_at_freelists.org
> put 'unsubscribe' in the subject line.
> --
> Archives are at http://www.freelists.org/archives/oracle-l/
> FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
> -----------------------------------------------------------------
Connor McDonald
Co-author: "Mastering Oracle PL/SQL - Practical Solutions" - available now
web: http://www.oracledba.co.uk
web: http://www.oaktable.net
email: connor_mcdonald_at_yahoo.com
"GIVE a man a fish and he will eat for a day. But TEACH him how to fish, and...he will sit in a boat and drink beer all day"
BT Yahoo! Broadband - Free modem offer, sign up online today and save £80
http://btyahoo.yahoo.co.uk
Please see the official ORACLE-L FAQ:
http://www.orafaq.com
To unsubscribe send email to: oracle-l-request_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Received on Wed Jan 28 2004 - 18:52:05 CST
