SQL & PL/SQL

This small piece of code will help you track down the PL/SQL block updating your table. Here we will use dbms utility dbms_utility.format_call_stack that gives us complete route of code being called to update the table. This code can be further modified for other triggers also. Do comment if it helps .

create or replace TRIGGER

xx_trigger

   AFTER UPDATE

   ON xx_table

   FOR EACH ROW

DECLARE PRAGMA

AUTONOMOUS_TRANSACTION;

l_sql_stmt VARCHAR2(4000) :='';

text_list ora_name_list_t := ora_name_list_t();

text_cnt binary_integer;

--

BEGIN

  BEGIN

    text_cnt           := ora_sql_txt(text_list);

    IF (text_list.count > 0) THEN

      l_sql_stmt       := SUBSTR (text_list.first,1,4000);

    END IF;

  EXCEPTION

  WHEN OTHERS THEN

    l_sql_stmt :='';

  END;

  INSERT

  INTO apps.xx_audit_table

    (

      INST_ID,

      SID,

      PROGRAM,

      MODULE,

      MACHINE,

      OSUSER,

      PROC_PROGRAM,

      SQL_ID,

      SPID,

      REDO_SIZE_MB,

      LOGON_TIME,

      LOGOFF_TIME,

      PGA_MAX_MB,

      CPU_USED,

      TERMINAL,

      IP_ADDRESS ,

      CLIENT_ID ,

      PORT,

      PROCESS ,

      SQL_TEXT ,

      CALL_STACK

    )

  SELECT

    /*+ ordered use_nl (sn s st p) */

    SYS_CONTEXT ('USERENV', 'INSTANCE') AS inst_id,

    s.sid,

    s.program,

    s.module,

    s.machine,

    s.osuser,

    p.program AS PROC_PROGRAM,

    s.prev_sql_id,

    p.spid,

    ROUND(st.value/1024/1024,2) AS redo_size_mb,

    s.logon_time,

    SYSDATE,

    p.PGA_MAX_MEM/1024/1024 AS PGA_MAX_MB,

    (SELECT st2.value

    FROM v$sesstat st2

    WHERE st2.sid      = s.sid

    AND st2.statistic# = 13

    AND rownum         = 1

    ) AS CPU_USED ,

    SYS_CONTEXT('USERENV','TERMINAL') ,

    SYS_CONTEXT('USERENV','IP_ADDRESS') ,

    SYS_CONTEXT('USERENV','CLIENT_IDENTIFIER') ,

    s.port ,

    s.process ,

    l_sql_stmt ,

    substrb (dbms_utility.format_call_stack,1,4000)

  FROM v$statname sn,

    v$session s,

    v$sesstat st,

    v$process p

  WHERE s.sid       = st.sid

  AND st.statistic# = sn.statistic#

  AND sn.name      IN ('redo size')

  AND s.paddr       = p.addr

  AND s.audsid      = USERENV ('SESSIONID');

  COMMIT;

EXCEPTION

WHEN OTHERS THEN

  null;

END;

The output of above code will be store in an custom audit table which you can create based on your requirement and columns you want to capture like old value new value etc. The call stack column will look like as below :

It gives you exact line from where API is being called.

Regards,

Ashish