RE: Analyze script written by Connor McDonald
- From: "Koivu, Lisa" <Lisa.Koivu@xxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 29 Jan 2004 08:25:12 -0500
Hi Connor,=20
Thanks for your response. Yes, the reason I was so confused was because =
I have never taken the time to learn the tables it's reading. =
Decomposing the sql statement didn't help much. I will probably tweak =
it a little bit to read the ALL* views instead. =20
Thanks again
Lisa
-----Original Message-----
From: Connor McDonald [mailto:hamcdc@xxxxxxxxxxx]=20
Sent: Wednesday, January 28, 2004 7:52 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: Analyze script written by Connor McDonald=20
Hi Lisa,
That'll teach you for using stuff written in 2002, especially when its =
based on a script written
in 2001 :-) =20
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. =20
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@xxxxxxxxxxxxxxx> wrote: > It's BEAUTIFUL! =
Best thing that's
happened all day!
> Thanks Jacques for your help. I'm so grateful!
> Lisa
>=20
> -----Original Message-----
> From: Jacques Kilchoer [mailto:Jacques.Kilchoer@xxxxxxxxx]=3D20
> Sent: Wednesday, January 28, 2004 3:40 PM
> To: oracle-l@xxxxxxxxxxxxx
> Cc: Koivu, Lisa
> Subject: RE: Analyze script written by Connor McDonald
>=20
> > -----Original Message-----
> > From: oracle-l-bounce@xxxxxxxxxxxxx
> > [mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Koivu, Lisa
> >=3D20
> > I ran it in debug mode also and it doesn't list any of the =
partitioned
> > tables within the schema I'm selecting.
>=20
> 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:
>=20
> 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) =3D3D 0
> and bitand(t.property,8192) =3D3D 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# (+) =3D3D so.file#
> and s.block# (+) =3D3D so.block#
> and s.ts# (+) =3D3D so.ts#
> and s.ts# =3D3D ts.ts# (+)
> and o.obj# =3D3D so.obj#
> and o.owner# =3D3D u.user#
> and s.type# (+) =3D3D so.segment_type_id
> and o.type# =3D3D so.object_type_id
> and s.ts# =3D3D f.ts# (+)
> and s.file# =3D3D f.relfile# (+)
> and u.name not in ('SYS','SYSTEM'))
> where owner =3D3D nvl(upper(p_owner),owner)
> and segment_type =3D3D nvl(upper(p_segment_type),segment_type)
> and mod(object_id,p_int1) =3D3D p_int2;
>=20
>=20
>=20
>=20
> "The sender believes that this E-Mail and any attachments were free of =
=3D
> any virus, worm, Trojan horse, and/or malicious code when sent. This =
=3D
> message and its attachments could have been infected during =3D
> transmission. By reading the message and opening any attachments, the =
=3D
> recipient accepts full responsibility for taking proactive and =
remedial =3D
> action about viruses and other defects. The sender's business entity =
is =3D
> not liable for any loss or damage arising in any way from this message =
=3D
> or its attachments."
>=20
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------=20
=3D=3D=3D=3D=3D
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@xxxxxxxxx
"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 =
=A380 http://btyahoo.yahoo.co.uk
----------------------------------------------------------------
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
-----------------------------------------------------------------
"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:
