RE: Analyze script written by Connor McDonald
- From: "Koivu, Lisa" <Lisa.Koivu@xxxxxxxxxxxxxxx>
- To: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>,<oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 28 Jan 2004 15:49:57 -0500
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@xxxxxxxxx]=20
Sent: Wednesday, January 28, 2004 3:40 PM
To: oracle-l@xxxxxxxxxxxxx
Cc: Koivu, Lisa
Subject: RE: Analyze script written by Connor McDonald
> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx]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@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
