Oracle: How can I know a table is getting populated?

Question

Hi,

I'm in charge of an oracle db where we don't have any documentation (at all). And at the moment I need to know HOW a table is getting populated. Ideally, I'd like to know from which procedure, trigger, whatever... this table gets its data from.

Any idea would be much appreciated.

Thanks.

Answer 1

It is quite simple ;-)

SELECT * FROM USER_SOURCE WHERE 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.

Answer 2

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.

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

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.

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