How to find out which package/procedure is updating a table?

Question

I would like to find out if it is possible to find out which package or procedure in a package is updating a table?

Due to a certain project being handed over (the person who handed over the project has since left) without proper documentation, data that we know we have updated always go back to some strange source point.

We are guessing that this could be a database job or scheduler that is running the update command without our knowledge. I am hoping that there is a way to find out where the source code is calling from that is updating the table and inserting the source as a trigger on that table that we are monitoring.

Any ideas?

Thanks.

Answer 1

UPDATE: I poked around and found out how to trace a statement back to its owning PL/SQL object.

In combination with what Tony mentioned, you can create a logging table and a trigger that looks like this:

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;
/

In order for the trigger above to compile, you'll need to grant the owner of the trigger these permissions, when logged in as the SYS user:

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

You will likely want to protect the insert statement in the trigger with some condition that only makes it log when the the change you're interested in is occurring - on my test server this statement runs rather slowly (1 second), so I wouldn't want to be logging all these updates. Of course, in that case, you'd need to change the trigger to be a row-level one so that you could inspect the :new or :old values. If you are really concerned about the overhead of the select, you can change it to not join against v$sql, and instead just save the SQL_ADDRESS column, then schedule a job with DBMS_JOB to go off and update the sql_text column with a second update statement, thereby offloading the update into another session and not blocking your original update.

Unfortunately, this will only tell you half the story. The statement you're going to see logged is going to be the most proximal statement - in this case, an update - even if the original statement executed by the process that initiated it is a stored procedure. This is where the program_id column comes in. If the update statement is part of a procedure or trigger, program_id will point to the object_id of the code in question - you can resolve it thusly:

SELECT * FROM all_objects where object_id = <program_id>;

In the case when the update statement was executed directly from the client, I don't know what program_id represents, but you wouldn't need it - you'd have the name of the executable in the "program" column of statement_tracker. If the update was executed from an anonymous PL/SQL block, I'm not how to track it back - you'll need to experiment further.

It may be, though, that the osuser/machine/program/module information may be enough to get you pointed in the right direction.