Check if a given DB object used in Oracle?
Hi does any one know how to check if a given DB object (Table/View/SP/Function) is used inside Oracle.
For example to check if the table "A" is used in any SP/Function or View definitions. I am trying to cleanup unused objects in the database.
I tried the query select * from all_source WHERE TEXT like '%A%' (A is the table name). Do you thing it is safe to assume it is not being used if it does not return any results?
Answer
From this ASKTOM question:
You'll have to enable auditing and then come back in 3 months to see.
We don't track this information by default -- also, even with auditing, it may be very possible to have an object that is INDIRECTLY accessed (eg: via a foreign key for example) that won't show up.
You can try USER_DEPENDENCIES but that won't tell you about objects referenced by code in client apps or via dynamic sql
There's code in the thread for checking ALL_SOURCE, but it's highlighted that this isn't a silver bullet.