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

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开发者_如何学C.

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

---

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.

---

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.

---

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#).

---

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

---

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.

---

Late to the party!

I second Gary's mention of v$sql also. That may yield the quick answer as long as the query hasn't been flushed.

If you know its in your current instance, I like a combination of what has been used above; if there is no dynamic SQL, xxx_Dependencies will work and work well.

Join that to xxx_Source to get that pesky dynamic SQL.

We are also bringing data into our dev instance using the SQL*Plus copy command (careful! deprecated!), but data can be introduced by imp or impdp as well. Check xxx_Directories for the directories blessed to bring data in/out.