------------------------------------------------------------
-- Victor Khalturin
-- https://dbpilot.net
--
-- Script Version 17:45 24-Dec-2023 (001)
--
-- dbt_sesstrace.sql - DATABASE TOOLS, A SESSION'S TRACE FILE
------------------------------------------------------------

SET LINES 150
SET PAGES 30
SET HEAD OFF
SET SQLNUMBER OFF
SET VERIFY OFF
COL SESSION_TRACE_DETAILS FOR A150

SELECT CHR(10) || RPAD('  -', 60, '-') || CHR(10) ||
       RPAD('  -- DATABASE', 20)  || ' : ' || 
            UPPER(SYS_CONTEXT ('USERENV', 'DB_NAME')) || '@' || 
            UPPER(SYS_CONTEXT ('USERENV', 'HOST')) || CHR(10) ||
       RPAD('  -- CURRENT TIME', 20)  || ' : ' || TO_CHAR(SYSDATE,'DD-MON-YYYY HH24:MI:SS') || CHR(10) ||
       RPAD('  -- USERNAME', 20)  || ' : ' || S.USERNAME || CHR(10) ||
       RPAD('  -- SESSION ID',20) || ' : ' || S.SID || ',' || S.SERIAL# || CHR(10) ||
       RPAD('  -- PROCESS ID',20) || ' : ' || P.SPID || CHR(10) ||
       RPAD('  -- LOGON TIME',20) || ' : ' || TO_CHAR(S.LOGON_TIME,'DD-MON-YYYY HH24:MI:SS') || CHR(10) ||
       RPAD('  -- SQL TRACE',20)  || ' : ' || S.SQL_TRACE || CHR(10) ||
       RPAD('  -- SQL WAITS',20)  || ' : ' || S.SQL_TRACE_WAITS || CHR(10) ||
       RPAD('  -- SQL BINDS',20)  || ' : ' || S.SQL_TRACE_BINDS || CHR(10) ||
       RPAD('  -- SIZE LIMIT',20) || ' : ' || 
       DECODE(L.VALUE,'unlimited','UNLIMITED', ROUND(L.VALUE/1024/1024) || ' MiB') || CHR(10) ||
       RPAD('  -- TRACE FILE',20) || ' : ' || P.TRACEFILE || CHR(10) ||
       RPAD('  -', 60, '-') || CHR(10) || CHR(10) ||
       CHR(10) || '  /* To Enable Tracing for the Session */' || CHR(10) || CHR(10) ||
       '  BEGIN' || CHR(10) || '    DBMS_MONITOR.SESSION_TRACE_ENABLE(' || CHR(10) ||
       '    SESSION_ID => ' || S.SID || ',' || CHR(10) || 
       '    SERIAL_NUM => ' || S.SERIAL# || ',' || CHR(10) ||
       '    WAITS => TRUE,' || CHR(10) || '    BINDS => TRUE,' || CHR(10) ||
       Q'[    PLAN_STAT => 'all_executions');]' || CHR(10) || '  END;' || CHR(10) ||
       '  /' || CHR(10) || CHR(10) || CHR(10) ||
       '  /* To Disable Tracing for the Session [Option #1] */' || CHR(10) || CHR(10) ||
       '  EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(SESSION_ID => ' || S.SID ||
       ', SERIAL_NUM => ' || S.SERIAL# || ');' || CHR(10) || CHR(10) || CHR(10) ||
       '  /* To Disable Tracing for the Session [Option #2] */' || CHR(10) || CHR(10) ||
       '  EXEC DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION(' || S.SID || ', ' || S.SERIAL# ||
       Q'[, 'SQL_TRACE', FALSE);]'|| CHR(10) || CHR(10) || CHR(10) ||
       '  /* To Increase File Size Limit */' || CHR(10) || CHR(10) ||
       '  ALTER SYSTEM SET MAX_DUMP_FILE_SIZE={INTEGER_BYTES|UNLIMITED} SCOPE=MEMORY;' || CHR(10) || CHR(10) SESSION_TRACE_DETAILS
FROM V$SESSION S, V$PROCESS P, V$PARAMETER L
WHERE S.PADDR=P.ADDR AND L.NAME='max_dump_file_size' AND S.SID=&SID;