RE: Analyze script written by Connor McDonald

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Wed, 28 Jan 2004 12:40:25 -0800
Message-ID: <B5C5F99D765BB744B54FFDF35F602621033DAE5F@irvmbxw02>

> -----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;

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 - 14:40:25 CST