How to find out which package / procedure updates the table?

Question

I would like to know if it is possible to find out which package or procedure in the package updates the table?

Due to the fact that some project was handed over (the person who handed over the project since then) without proper documentation, the data that we know we updated always returns to some strange starting point.

We assume that this could be a database task or a scheduler that runs an update command without our knowledge. I hope there is a way to find out where the source code comes from, by updating the table and inserting the source as a trigger in the table that we are tracking.

Any ideas?

Thanks.

Answer 1

: PL/SQL.

, , , :

CREATE TABLE statement_tracker
( SID NUMBER
, serial# NUMBER
, date_run DATE
, program VARCHAR2(48) null
, module VARCHAR2(48) null
, machine VARCHAR2(64) null
, osuser VARCHAR2(30) null
, sql_text CLOB null
, program_id number
);

CREATE OR REPLACE TRIGGER smb_t_t
   AFTER UPDATE
   ON smb_test
BEGIN
   INSERT 
     INTO statement_tracker
   SELECT ss.SID
        , ss.serial#
        , sysdate
        , ss.program
        , ss.module
        , ss.machine
        , ss.osuser
        , sq.sql_fulltext
        , sq.program_id
     FROM v$session ss
        , v$sql sq
    WHERE ss.sql_address = sq.address
      AND ss.SID = USERENV('sid');
END;
/

, SYS:

grant select on V_$SESSION to <user>;
grant select on V_$SQL to <user>;

, insert , , , , - (1 ), . , , : . , , v $sql, SQL_ADDRESS, DBMS_JOB, sql_text , .

, . , , - - , , , . program_id. , program_id object_id - :

SELECT * FROM all_objects where object_id = <program_id>;

, , , program_id, - "program" statement_tracker. PL/SQL, - .

, , osuser/machine/program/module , .

Answer 2

, , , , . , , :

• , . ALL_DEPENDENCIES, , / .. . , .

• :

  , all_source (), ( "% mytable%" );

, , , , " ", , . - , :

select distinct type, name
from   all_source
where lower(text) like lower('%insert into mytable%');

, , , -.

, SQL- "cron" ?

Answer 3

" " "DBMS_UTILITY.FORMAT_CALL_STACK" .

- , .   , , Google, .

Answer 4

The quick and dirty option, if you work locally and are only interested in what changes the data, is to throw an error into the trigger instead of logging. This way you get a regular stack trace, and it prints a lot less, and you don't need to create a new table:

AFTER UPDATE ON table_of_interest
BEGIN
    RAISE_APPLICATION_ERROR(-20001, 'something changed it');
END;
/