Tacking Table Updation

3152606

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 :

7000113d6304a50	1  anonymous block
700011523578b68	17  APPS.XX_TRIGGER
7000114b8585c38	497  package body APPS.HZ_CUST_SITE_USES_PKG
70001171efc4738	1832  package body APPS.HZ_CUST_ACCOUNT_SITE_V2PUB
70001171efc4738	3094  package body APPS.HZ_CUST_ACCOUNT_SITE_V2PUB
700011447e7cb48	547  package body APPS.XX**********_PKG
700011447e7cb48	75  package body APPS.XX*********_PKG
7000115a3886338	1  anonymous block
70001173e311050	537  package body APPS.XX***************_PKG
7000114fc360880	1  anonymous block

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

Regards,

Ashish