This content has been marked as final. Show 8 replies
LuKKa wrote:Handle: LuKKa
How can I know that a table is using in any packege or procedure or function ?
I mean , let say I have 10 Packages , 10 Procedures and 5 Functions .
I need to know in which Packages , Procedures or Functions are using the table EMP .
How can I search ? Is there any command or I need to open all the procedures and then search one by one ?
Status Level: Newbie
Registered: Dec 23, 2009
Total Posts: 283
Total Questions: 137 (105 unresolved)
why so many unanswered questions?
you can use the following sql query. user_dependencies / all_dependencies / dba_dependencies, these can also be used.
select * from all_dependencies where referenced_name ='your_table_name'
select referenced_owner, referenced_type, referenced_name, owner, type, name from dba_dependencies where referenced_owner like upper('&1') and referenced_name like upper('&2') and referenced_owner != 'SYS' and referenced_type != 'NON-EXISTENT'order by owner, type, name;
Though you have to remember that checking the dependencies via the dependency views only works for static SQL's in the code... but if the code contains any dynamic SQL where the table names are in strings or picked up from parameters or tables, then Oracle won't know of such dependencies.
I tried with USER_SOURCE .
It is also working fine .
... but USER_SOURCE could give you false hits and still miss some things unless you write your own SQL parser. Some examples:
false positive for EMP
another false positive for EMP
/* select count(*) into foo from EMP */
you'd miss EMP if FOOBAR is a synonym for EMP or a view that uses EMP
select count(*) from employees EMP
false positive again
select count(*) from FOOBAR;
and so on...
declare EMP number;
Did anyone interest in the topic search for dependencies between objects in the database?
I want to see the PL/SQL code which is an appeal to a particular procedure or function or package and where and how does an object use in any PL/SQL code.
Often do you have a need to get answers to these questions?
I say about an object usage presentation, not about its relations only.
Do you want to know, how opbject used?
If it's a table - what's DML?(insert/select/...)
If it used in a package - which procedure of package uses in?
The object usage in an external objects - Oracle*Forms/Reports,Flat/Word files, Informatica.
I'll want to know, if everybody interested to one.
Digging one month old thread?