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 i.obj#,
                  i.symrep name,
                  row_number() over(partition by i.obj# order by a.action#) 
           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).

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

(co)Author: "Applied Mathematics for Database Professionals"<>

Other related posts: