Solved

How do I find all funtions/procedures/packages which write/update/read to/from a table?

Posted on 2014-01-28
4
500 Views
Last Modified: 2014-01-29
How do I find all funtions/procedures/packages which write/update/read to/from a table?
I dont want to audit the table as yet...
0
Comment
Question by:Rao_S
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
4 Comments
 
LVL 15

Accepted Solution

by:
Franck Pachot earned 300 total points
ID: 39814845
Hi,
As an example, the following will list all procedure/function/package that references the SYS.DUAL table:

select owner,name,type from dba_dependencies 
where referenced_owner='SYS' and referenced_name='DUAL' 
and type like 'PACKAGE%' or type in ('PROCEDURE','FUNCTION');

Open in new window


Regards,
Franck.
0
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 100 total points
ID: 39814876
dba_dependencies will work as long as the procedures do not use dynamic sql with an execute immediate.

If that is a possibility you need to look for the table name in the dba_source view with an INSTR of REGEXP_INSTR call.
0
 
LVL 35

Assisted Solution

by:johnsone
johnsone earned 100 total points
ID: 39814886
If dynamic SQL is a possibility, even a search of DBA_SOURCE isn't foolproof.  The name of the object could be a parameter passed from an external program and wouldn't be in the source code.
0
 

Author Closing Comment

by:Rao_S
ID: 39819640
thank you all, franck's solution worked great for me, i dint try the other 2 solutions, but they are very good information to have..
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to recover a database from a user managed backup

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question