• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 515
  • Last Modified:

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

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
Rao_S
Asked:
Rao_S
3 Solutions
 
Franck PachotCommented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
johnsoneSenior Oracle DBACommented:
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
 
Rao_SAuthor Commented:
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

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now