How to Retrieve Call Stack Without Using Trace_SYS in Background Jobs?

Hi ​@WIJNLK ,

You can use Autonomous Trace to capture the callstack in the situations where trace_sys cannot be used. Please follow the steps below.

Execute steps 1, 2 and 3 sequentially in the database where you need to perform the investigation.

1. Create the Sequence

CREATE SEQUENCE TEST_AUTONOMOUS_TRACE_SEQ INCREMENT BY 1 START WITH 1 MINVALUE 1 MAXVALUE 100000000 CYCLE CACHE 2;

2. Create the Table

DECLARE columns_ Database_SYS.ColumnTabType; BEGIN Database_SYS.Reset_Column_Table(columns_); Database_SYS.Set_Table_Column(columns_, 'TRACE_seq', 'NUMBER', 'Y'); Database_SYS.Set_Table_Column(columns_, 'TRACE_ID', 'VARCHAR2(30)', 'Y'); Database_SYS.Set_Table_Column(columns_, 'TEXT', 'VARCHAR2(2000)', 'Y'); Database_SYS.Set_Table_Column(columns_, 'TIMESTAMP', 'DATE', 'Y'); Database_SYS.Set_Table_Column(columns_, 'os_user', 'DATE', 'Y'); Database_SYS.Create_Table('TEST_AUTONOMOUS_TRACE_TAB', columns_); END; /

3. Create the Autonomous Procedure

CREATE OR REPLACE PROCEDURE test_autonomous_trace( trace_id_ IN VARCHAR2, text_ IN VARCHAR2) AS PRAGMA AUTONOMOUS_TRANSACTION; CURSOR get_os_user IS SELECT t.osuser FROM v$session t WHERE t.audsid = userenv('SESSIONID'); os_user_ VARCHAR2(20); BEGIN INSERT INTO TEST_AUTONOMOUS_TRACE_TAB (trace_seq, trace_id, text, TIMESTAMP, os_user) VALUES (TEST_AUTONOMOUS_TRACE_SEQ.nextval, SUBSTR(trace_id_, 1, 30), SUBSTR(text_, 1, 2000), SYSDATE, os_user_); COMMIT; END test_autonomous_trace; /

4. Capturing the Call Stack

Use the following code snippet in any PL/SQL section where you want to capture the call stack:

test_autonomous_trace( 'TEST', SUBSTR(dbms_utility.format_call_stack, 1, 2000) );

This approach allows you to log trace information independently of the main transaction, which is especially useful when normal tracing tools are not applicable.

Hope this is helpful.