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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With