When tracing another user's session, How can I check if SESSION TRACING is currently enabled for that session?
GOAL

To know if SQL TRACE is enabled or disabled in a specific session.
To get information about session's trace file location.

SCRIPT

These kinds of information can be obtained by DBT_SESSTRACE script

SQL> @dbt_sesstrace.sql
Enter value for sid: 270


  ----------------------------------------------------------
  -- DATABASE        : TST12CR1@DBPILOT.VMWARE.LOCAL
  -- CURRENT TIME    : 24-DEC-2023 18:49:10
  -- USERNAME        : SYS
  -- SESSION ID      : 270,3073
  -- PROCESS ID      : 5475
  -- LOGON TIME      : 24-DEC-2023 15:34:47
  -- SQL TRACE       : ENABLED
  -- SQL WAITS       : TRUE
  -- SQL BINDS       : TRUE
  -- SIZE LIMIT      : 10 MiB
  -- TRACE FILE      : /u01/app/oracle/diag/rdbms/tst12cr1/tst12cr1/trace/tst12cr1_ora_5475.trc
  ----------------------------------------------------------


  /* To Enable Tracing for the Session */

  BEGIN
    DBMS_MONITOR.SESSION_TRACE_ENABLE(
    SESSION_ID => 270,
    SERIAL_NUM => 3073,
    WAITS => TRUE,
    BINDS => TRUE,
    PLAN_STAT => 'all_executions');
  END;
  /



  /* To Disable Tracing for the Session [Option #1] */

  EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(SESSION_ID => 270, SERIAL_NUM => 3073);


  /* To Disable Tracing for the Session [Option #2] */

  EXEC DBMS_SYSTEM.SET_BOOL_PARAM_IN_SESSION(270, 3073, 'SQL_TRACE', FALSE);


  /* To Increase File Size Limit */

  ALTER SYSTEM SET MAX_DUMP_FILE_SIZE={INTEGER_BYTES|UNLIMITED} SCOPE=MEMORY;

Written At
24 DECEMBER 202318:50
Red Hat Release
7.x x64
Database Release
12.1.0.2.0