Get list of tables used in a complex query

Asked Sep 28 '17 at 13:26

Active Sep 28 '17 at 13:26

Viewed 73 times

Suppose there is an oracle query which calls an function

select * from table(xyz(to_date('10/04/2017', 'dd/mm/yyyy')))

and function xyz has two select statements in it

 select * from A

and

select * from B

So this query references(reads/writes/updates) two tables A and B

Is there a way to know the number of tables that are referenced when I run this query on oracle ?

asked Sep 28 '17 at 13:26

Abhishek Singh

I don't see any way for this unless you have a look at code of function xyz. – Wernfried Domscheit Sep 28 '17 at 13:45
There is one for SQL. – Abhishek Singh Sep 28 '17 at 13:57
If I understood your question correctly then you can always query `{ dba | all | user }_dependencies` data dictionary view to get that information. – Nick Krasnov Sep 28 '17 at 14:18
Possible duplicate of [How can you tell if a PL/SQL Package, Procedure, or Function is being used?](https://stackoverflow.com/questions/4670164/how-can-you-tell-if-a-pl-sql-package-procedure-or-function-is-being-used) – XING Sep 28 '17 at 15:22
Not a duplicate question. My Question is different. I want to extract the list of tables in each query. – Abhishek Singh Sep 29 '17 at 06:42

0 Answers0