[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2014-01-28
4
Medium Priority
?
512 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 1200 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 400 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 400 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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video shows how to recover a database from a user managed backup

656 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