RE: Oracle 11g and fine-grained invalidation
- From: Michael Rosenblum <mrosenblum@xxxxxxxxxxx>
- To: "toon.koppelaars@xxxxxxxxxxx" <toon.koppelaars@xxxxxxxxxxx>
- Date: Thu, 9 Sep 2010 10:39:23 -0400
I will definitely talk to Bryn - but I am still wondering how many people care
about possibility of reporting on fine-grained dependencies...
Btw, last night I already started to work with PL/Scope data and looks like
I've got at least something out of it.
I modified the original Rob's query in the following way:
select
.....
from
.....
,( select i.obj#,
i.symrep name,
row_number() over(partition by i.obj# order by a.action#)
procedure#
from sys.plscope_identifier$ i,
sys.plscope_action$ a
where i.signature = a.signature
and a.action=1 -- declaration
and context#=1 -- root elements
) c
where d.obj# = c.obj#
and d.colpos = c.procedure#
And from the first impression, it pretty much does what it is supposed to do. I
didn't have time to run a deep test, but a couple of basic cases (referencing
procedure/type/variable from one package to another) passed a sanity check.
I will try to get more out of it (if time permits).
Regards,
Michael Rosenblum
Dulcian Inc
P.s. Fellow Oracle-L members, if you DO care about fine-grained dependencies -
I would love to have your responses here (maybe even with business cases)! It
will give a bit more weight to my request. Thanks in advance!
-----Original Message-----
From: Toon Koppelaars [mailto:toon.koppelaars@xxxxxxxxxxx]
Sent: Wednesday, September 08, 2010 11:59 PM
To: Michael Rosenblum
Cc: oracle-l@xxxxxxxxxxxxx
Subject: Re: Oracle 11g and fine-grained invalidation
Unfortunately if in the package there are declarations of
types/variables/cursors, the whole count is off by the number of non-procedural
unit – and I at the current point I could not find any other metadata table to
get what I need.
So now, you'd have to employ plscope to provide you with the metadata, that
enables you to find this offset...
And yes, definitely seek out Bryn, and ask him to provide us with proper data
dictionary based insight for this area.
--
Toon Koppelaars
RuleGen BV
Toon.Koppelaars@xxxxxxxxxxx
www.RuleGen.com<http://www.rulegen.com/>
TheHelsinkiDeclaration.blogspot.com<http://thehelsinkideclaration.blogspot.com/>
(co)Author: "Applied Mathematics for Database Professionals"
www.RuleGen.com/pls/apex/f?p=14265:13<http://www.rulegen.com/pls/apex/f?p=14265:13>
Other related posts:
