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 :
Regards,
Ashish