Solved

sql to find references to a table

Posted on 2012-04-11
13
436 Views
Last Modified: 2012-04-30
hi

Is there a sql to find out all the store procedures which are using a table?

thanks
0
Comment
Question by:royjayd
  • 5
  • 5
  • 2
  • +1
13 Comments
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 143 total points
ID: 37832741
The view you need is DBA_DEPENDENCIES.

http://www.dba-oracle.com/d_dba_dependencies.htm
0
 
LVL 8

Assisted Solution

by:Christoffer Swanström
Christoffer Swanström earned 72 total points
ID: 37832811
SELECT
  *
FROM
  dba_dependencies
WHERE
  referenced_name = 'YOUR_TABLE_NAME'
AND
  type IN ('PACKAGE BODY', 'PROCEDURE', 'FUNCTION')
;

This will show you all packages, procedure and functions that reference your table.

Note that you will NOT be able to find procedures, functions or packages that reference a table using dynamic SQL. E.g. if your procedure contains EXECUTE IMMEDIATE 'TRUNCATE YOUR_TABLE_NAME', the above query will not show this dependency.
0
 
LVL 8

Assisted Solution

by:Christoffer Swanström
Christoffer Swanström earned 72 total points
ID: 37832831
If you want to find procedure, functions or packages where the name of the table is hard coded in a dynamic SQL statement you can query all_source:

SELECT
  *
FROM
  all_source
WHERE
  UPPER(text) LIKE '%YOUR_TABLE_NAME%'
;

Of course this will only find the cases where the table name is hard coded. If it is passed as an argument or otherwise generated dynamically you will not find it.
0
 

Author Comment

by:royjayd
ID: 37833133
thanks

when i do
SELECT
  *
FROM
  dba_dependencies
WHERE
  referenced_name = 'PERSON_DATA'  AND
  type IN ('PACKAGE BODY', 'PROCEDURE', 'FUNCTION')
;

i get error table does not exist

however when i replace dba_dependencies with all_dependencies
i get some results

why is that
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 143 total points
ID: 37833247
"dba_" level views require DBA level access and show EVERYTHING in the database

"all_" level views are the objects you own and have rights to access

"user_" views are the objects you own.
0
 
LVL 31

Assisted Solution

by:awking00
awking00 earned 35 total points
ID: 37833257
It's because you don't have permissions to access the dba dictionary tables or views. You need to have the 'select any dictionary' privilege.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:royjayd
ID: 37833398
>>>It's because you don't have permissions to access the dba dictionary tables or views. You need to have the 'select any dictionary' privilege.


what exactly do you mean by dictionary ? Do you mean schema ?


thx all.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37833415
The data dictionary are all the system views/tables about things (metadata).  A schema is a user that owns objects.  Two different things.

http://en.wikipedia.org/wiki/Data_dictionary
0
 

Author Comment

by:royjayd
ID: 37834630
one quick question

when i run
SELECT * FROM all_dependencies WHERE  referenced_name = 'GET_STATUS' AND type IN ('PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'TYPE');

i get no data found

But i see 'GET_STATUS' is defined under function which is being used by a procedure body.

any idea why?
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 143 total points
ID: 37834652
Works for me:
create or replace function myfunc return varchar2 
is
begin
	return 'Hello';
end;
/

show errors

create or replace procedure myproc
is
begin
	dbms_output.put_line(myfunc);
end;
/

show errors


SELECT * FROM all_dependencies WHERE  referenced_name = 'MYFUNC' AND type IN ('PACKAGE BODY', 'PROCEDURE', 'FUNCTION', 'TYPE');

Open in new window


Does your setup resemble my test case?
0
 

Author Comment

by:royjayd
ID: 37834915
>>Does your setup resemble my test case?


Does it have to do something with grants? Do i need to have grants to all the functions ?
0
 

Author Comment

by:royjayd
ID: 37834918
and yes,.. my set up is exactly like yours
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 143 total points
ID: 37837326
If it was a permission issue you would get a permissions error.

Remember the ALL_ views are just objects you either own or have been granted access to.

What is the result of:
select owner, object_type from all_objects where object_name='GET_STATUS';
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

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…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines

743 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now