Ever want to drop an old procedure, but you didn't know if it was being used by some other code, perhaps in another schema? Simply check ALL_DEPENDENCIES, right. Except I hate typing those long column names. And what if I want to traverse through the dependencies? So of course, I wrote some scripts.

Never sure what's a dependency vs a reference, so I named them the way I think it works, but they might be backwards.

SHOW_DEPENDENCIES - shows what objects my code (object) uses, and continues down the tree.
SHOW_REFERENCES - shows what objects call my code (object), and continues up the tree.

Both take two parameters: &1 = owner, &2 = object (if you don't know the owner, try the OBJ_TREE script I posted earlier). And both scripts work on the underlying sys objects, so you need to be a DBA (I do a "connect by" on sys.dependency$, since the connect by on ALL_DEPENDENCIES never finished). DEPENDENCIES stops when it finds something owned by SYS, because it just gets too messy to keep showing things (remove the "d_onam <> 'SYS'" clause near the bottom to see for yourself).

An example using some FLOWS_010500 objects (htmldb provides such nice examples):
SQL> @show_dependencies FLOWS_010500 p
.
.PROCEDURE FLOWS_010500.P
. PACKAGE FLOWS_010500.WWV_FLOW_LANG
. SYNONYM PUBLIC.HTP
. PACKAGE FLOWS_010500.WWV_FLOW_FILE_MGR

PL/SQL procedure successfully completed.

here's what happens if I remove the check against SYS objects
SQL> /
.
.PROCEDURE FLOWS_010500.P
. PACKAGE SYS.STANDARD
. PACKAGE FLOWS_010500.WWV_FLOW_LANG
. PACKAGE SYS.STANDARD
. SYNONYM PUBLIC.HTP
. PACKAGE SYS.HTP
. PACKAGE SYS.STANDARD
. PACKAGE FLOWS_010500.WWV_FLOW_FILE_MGR
. PACKAGE SYS.STANDARD
. PACKAGE SYS.SYS_STUB_FOR_PURITY_ANALYSIS

PL/SQL procedure successfully completed.

and here's one going the other direction
SQL> @show_references FLOWS_010500 WWV_FLOW_FILE_MGR
.
.PACKAGE FLOWS_010500.WWV_FLOW_FILE_MGR
. PACKAGE BODY FLOWS_010500.WWV_FLOW_FILE_MGR INVALID
. PACKAGE BODY FLOWS_010500.HTMLDB_UTIL
. PROCEDURE FLOWS_010500.P
. SYNONYM PUBLIC.P
. SYNONYM PUBLIC.WWV_FLOW_FILE_MGR
.
.PACKAGE BODY FLOWS_010500.WWV_FLOW_FILE_MGR INVALID

PL/SQL procedure successfully completed.

the REFERENCES script is also nice to find out where a table is used
SQL> @show_references FLOWS_010500 WWV_FLOW_PREFERENCES$
.
.TABLE FLOWS_010500.WWV_FLOW_PREFERENCES$
. TRIGGER FLOWS_010500.WWV_BIU_FLOW_PREFERENCES
. FUNCTION FLOWS_010500.V
. TRIGGER FLOWS_010500.WWV_BIU_FLOW_COLLECTION INVALID
. FUNCTION FLOWS_010500.NV
. VIEW FLOWS_010500.WWV_FLOW_USERS
. SYNONYM PUBLIC.WWV_FLOW_USERS
. VIEW FLOWS_010500.WWV_FLOW_GROUP_USERS
that one continues on for quite some time

These scripts work on versions 9 and 10. Haven't had the chance to test them on 11 yet, so if you do, and they don't work, let me know. Or better yet, fix them for me ;)

Follow the rest of the discussion on my blog

Views: 1058

Attachments:

Replies to This Discussion

By applying some CSS magic, the extra blank lines inside the pre tags are no more. Great posts by the way. Keep them coming :) I'm also adding your posts here to OraNA.info.
thanks for the magic. the output looks much better now

I had not heard of OraNA.info before - I'll have to spend some time over there. Can you update the post over there to link back here (because the scripts aren't there)?
If you click on any post title on OraNA.info, it links back to the original article.
This is great... is there a way to figure out the CRUD operations done on a table by a plsql unit? There are times, when in addition to the depenedency I need to know if the table is updated, or just selected by the unit.

RSS

Oracle Community On

I'm not a fan of advertising, and so I will not be including any advertisements on OracleCommunity.net. However, managing this community does not come without cost! If you are willing to donate to help pay for the monthly community fees and domain services I accept Bitcoin and PayPal donations.

Donate Bitcoins

Oracle Blogosphere

Loading… Loading feed

Badge

Loading…

Blog Posts

Do What You Do Best and Outsource the Rest

One of the most valuable assets of an organization, data lies at the heart of business. It impacts everything from customer service to email deliverability and ultimately revenue generation. If the data that you have is not accurate, it will have a direct impact on your company’s ability to meet…

Continue

Posted by Alvaro Dee on February 7, 2017 at 2:07am

Draw Actionable Insights and Make Better Decisions with Data Mining Services

Data Mining is the process of analyzing and extracting crucial information from a database and presenting the same in an appropriate way to help business cut cost and boost revenue. In simple words it is the process of drawing crucial and actionable insights, which is imperative for better decision making.

Data mining is crucial for effective management of your business. It is also used for verifying whether or not the strategies are going as planned so as to provide desired results.…

Continue

Posted by Alvaro Dee on February 1, 2017 at 4:02am

Attain Data Accuracy, Competitive Pricing, and Quick Turnaround Time by Outsourcing Data Entry Services

In order to focus on crucial business activities, organizations worldwide prefer outsourcing data entry services to reliable service providers. Though redundant but highly crucial, data entry still remains an important component of document management and requires vigorous quality checks. Outsourcing data entry tasks to experts ensures affordable and quality output while saving time and resources that can be…

Continue

Posted by Alvaro Dee on January 31, 2017 at 4:49am

Present and Exchange Documents Reliably With Portable Document Format

Are you having data in large volumes that needs to be converted from hard copy to PDF format? Can you imagine how much time it would take you to manually convert this data? Well, if you know, then what’s the point of putting in lot of efforts and time?

With the help of PDF data entry services, this task can be made a lot easier. Data entry experts can help you save time and efforts…

Continue

Posted by Alvaro Dee on January 25, 2017 at 3:38am

© 2017   Created by Steve Karam.   Powered by

Badges  |  Report an Issue  |  Terms of Service