How to find out where the database table is populated?

Question

I am responsible for the Oracle database for which we have no documentation. At the moment I need to know how the table is populated.

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

Answer 1

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

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

And yes, you need to see through objects to see if INSERT is happening.

Also this is from my comment above.

If this is not a production system, I would suggest that you raise the user A specific exception in TRIGGER- before INSERT with some user message or LOCK the table from INSERT and watch for applications that try to insert a failure in them. But yes, you can also get calls from many evil people.

Answer 2

It's pretty simple ;-)

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

In the output, you will have all the procedures, functions, etc. that in your body call your table named NAME_OF_YOUR_TABLE.

NAME_OF_YOUR_TABLE must be written as UPPERCASE because we use UPPER (TEXT) to get the results in the form of Name_Of_Your_Table, NAME_of_YOUR_table, NaMe_Of_YoUr_TaBlE and so on.

Answer 3

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

Answer 4

DML changes are recorded in * _TAB_MODIFICATIONS.

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

Using a trigger, you can write SYS_CONTEXT variables to a table.

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

Answer 5

It looks like you want to conduct an audit.

What about

 AUDIT ALL ON ::TABLE::; 

Alternatively, apply the DBMS_FGA policy in the table and collect the client, program, user, and, possibly, the call stack will also be available.

Answer 6

Late to the party!

Gary's second mention of v $ sql as well. This can give a quick response if the request has not been reset.

If you know it in your current instance, I like the combination of what was used above; if dynamic SQL does not exist, xxx_Dependencies will work and work well.

Attach this to xxx_Source to get this annoying dynamic SQL.

We also cast the data to our dev instance using the SQL * Plus copy command (carefully! Obsolete!), But the data can be entered by imp or impdp. Check xxx_Directories for directories available for input / output.