Solved

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

Posted on 2014-01-28
4
505 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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video shows how to configure and send email from and Oracle database using both UTL_SMTP and UTL_MAIL, as well as comparing UTL_SMTP to a manual SMTP conversation with a mail server.

687 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