How can I find out where a database table is being populated from?

Question

I'm in charge of an Oracle database for which we don't have any documentation. At the moment I need to know how a table is getting populated.

How can I find out which procedure, trigger, or other source, this table is getting its data from?

Answer 1

Or even better, query the DBA_DEPENDENCIES table (or its equivalent USER_ ). You should see what objects are dependent on them and who owns them.

select owner, name, type, referenced_owner
from dba_dependencies
where referenced_name = 'YOUR_TABLE'

And yeah, you need to see through the objects to see whether there is an INSERT happening in.

Also this, from my comment above.

If it is not a production system, I would suggest you to raise an user defined exception in TRIGGER- before INSERT with some custom message or LOCK the table from INSERT and watch over the applications which try inserting into them failing. But yeah, you might also get calls from many angry people.

Answer 2

It is quite simple ;-)

SELECT * FROM USER_SOURCE WHERE UPPER(TEXT) LIKE '%NAME_OF_YOUR_TABLE%';

In output you'll have all procedures, functions, and so on, that in ther body invoke your table called NAME_OF_YOUR_TABLE.

NAME_OF_YOUR_TABLE has to be written UPPERCASE because we are using UPPER(TEXT) in order to retrieve results as Name_Of_Your_Table, NAME_of_YOUR_table, NaMe_Of_YoUr_TaBlE, and so on.