Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Find the source of data for certain table - ORACLE

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.

like image 721
Hawk Avatar asked Feb 12 '23 08:02

Hawk


2 Answers

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.

like image 166
Rahul Tripathi Avatar answered Feb 15 '23 09:02

Rahul Tripathi


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.

like image 21
Frank Schmitt Avatar answered Feb 15 '23 09:02

Frank Schmitt