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?

Solution 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.

Solution 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.

Solution 3

Another thought is to try querying v$sql to find a statement that performs the update. You may get something from the module/action (or in 10g progam_id and program_line#).

Solution 4

Sounds like you want to audit.

How about

AUDIT ALL ON ::TABLE::;

Alternatively apply DBMS_FGA policy on the table and collect the client, program, user, and maybe the call stack would be available too.

Solution 5

DML changes are recorded in *_TAB_MODIFICATIONS.

Without creating triggers you can use LOG MINER to find all data changes and from which session.

With a trigger you can record SYS_CONTEXT variables into a table.

http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions165.htm#SQLRF06117

View more solutions