Find the source of data for certain table - ORACLE

Question

This might be trivial question. But as I am working on a DB that was created by others long time ago, no proper documentation or comments included, I came a cross a critical question, I need to know how data is being inserted into certain table? Is there any script or other methods that can identify the data source. In other words, I need to know whether data is inserted via some procedure, function, manually ... etc. I cannot search all procedures or functions, they are hundreds. I am using SQL developer and it's oracle 11g DB.

Solution 1

No there is no such script through which you can determine the source of the data for your table. The best I can think of is that you can filter all the stored procedures where your table is referenced

SELECT *
  FROM dba_dependencies
 WHERE referenced_owner = 'SCOTT'
   AND referenced_name  = 'YOUR_TABLE_NAME'
   AND referenced_type  = 'TABLE'

or you can use this script

SELECT *
  FROM dba_source
 WHERE UPPER(text) LIKE '%YOUR_TABLE_NAME%';

This will filter out all the stored procs/triggers/other db code in which your table is referenced then you have to check in which code your insert statement is used.

Solution 2

Adding a trigger on the table that logs the PL/SQL call stack with dbms_utility.format_call_stack() might give you the information you need; here's an example that logs all inserts into the INS_TEST table (the log is contained in INS_LOG):

create table ins_test (pk number not null primary key);

create table ins_log(pk number not null primary key,
  text varchar2(4000));

create sequence seq_ins;
create sequence seq_log;  

create or replace trigger tr_air_ins_test after insert on ins_test
for each row
begin
  insert into ins_log(pk, text) values (
    seq_log.nextval, 
    dbms_utility.format_call_stack
  );
end;

create or replace procedure proc1 as
begin
  insert into ins_test values (seq_ins.nextval);
end;

create or replace procedure proc2 as
begin
  insert into ins_test values (seq_ins.nextval);
end;

begin
  proc1;
  proc2;
end; 

insert into ins_test values (seq_ins.nextval);

But before using this, you should run the SQL statements suggested by R.T. - it's easier and doesn't impact your database and might be sufficient.