ORA-00017: Session Requested to Set Trace Event - Manage Diagnostic Tracing

ORA-00017: Session Requested to Set Trace Event

Error Overview

Error Text: ORA-00017: session requested to set trace event

The ORA-00017 error is an informational signal used internally by Oracle to instruct a session to set a trace event on itself. Unlike most ORA- errors, this is not typically a fatal error that users encounter in the normal course of database operations — instead, it is a mechanism by which the Oracle background process infrastructure communicates tracing instructions between sessions. It commonly appears in alert logs and trace files when a DBA uses ORADEBUG or ALTER SESSION SET EVENTS to initiate diagnostic tracing on another session.

Understanding ORA-00017 is important for DBAs who perform root-cause analysis, since it appears alongside 10046 (SQL trace), 10053 (optimizer trace), and other Oracle diagnostic events. Misinterpreting it as a real error can cause unnecessary alarm.

Common Causes

1. DBA-Initiated Session Tracing

• A DBA executed ALTER SYSTEM SET EVENTS or ORADEBUG targeting another session
• Diagnostic tracing requested via Enterprise Manager or another monitoring tool
• An automated monitoring framework enabled event tracing on active sessions

2. Oracle Internal Diagnostic Mechanisms

• Oracle Support requested tracing as part of a Service Request (SR) investigation
• The Oracle kernel signaled a session to enable internal diagnostic events
• DIAG background process communicating trace instructions to a foreground session

3. 10046 SQL Trace Activation

• ALTER SESSION SET EVENTS '10046 trace name context forever, level 12' issued against a session
• Wait event tracing enabled on a session from outside that session
• Bind variable capture activated for performance diagnostics

4. 10053 Optimizer Trace Activation

• CBO (Cost-Based Optimizer) trace requested to diagnose a bad execution plan
• ALTER SESSION SET EVENTS '10053 trace name context forever' used on a remote session
• Optimizer trace requested during a plan stability investigation

5. Alert Log Appearances

• The error appears in the alert log when Oracle records that a trace event signal was delivered
• Spikes in ORA-00017 in alert logs can indicate automated monitoring tools running aggressive tracing
• Application code that inadvertently triggers session events can generate multiple entries

Diagnostic Queries

Check Active Trace Events on Sessions

-- View active trace events for all current sessions
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.status,
  s.program,
  s.machine,
  s.event,
  se.name        AS trace_event_name,
  se.value       AS trace_event_value
FROM v$session s
LEFT JOIN v$ses_optimizer_env se ON s.sid = se.sid
WHERE s.username IS NOT NULL
ORDER BY s.sid;

-- Check for sessions currently writing trace files
SELECT
  s.sid,
  s.serial#,
  s.username,
  s.sql_trace,
  s.sql_trace_waits,
  s.sql_trace_binds,
  s.status,
  s.program,
  s.machine
FROM v$session s
WHERE s.sql_trace = 'ENABLED'
   OR s.sql_trace_waits = 'TRUE'
   OR s.sql_trace_binds = 'TRUE'
ORDER BY s.sid;

Identify Trace Files Being Generated

-- Find active trace files (requires diagnostic_dest to be set)
SELECT
  value AS diagnostic_dest
FROM v$parameter
WHERE name = 'diagnostic_dest';

-- Check current trace file for your own session
SELECT
  d.value || '/diag/rdbms/'
    || LOWER(i.db_unique_name) || '/'
    || LOWER(i.instance_name)
    || '/trace/' AS trace_dir
FROM v$diag_info d, v$instance i
WHERE d.name = 'Diag Trace';

-- List recent trace events in the unified diagnostic repository
SELECT
  originating_timestamp,
  component_id,
  message_text
FROM v$diag_alert_ext
WHERE originating_timestamp > SYSDATE - 1/24
  AND message_text LIKE '%ORA-00017%'
ORDER BY originating_timestamp DESC;

Check Alert Log for ORA-00017 Frequency

-- Scan alert log entries referencing trace events (via external table if configured)
SELECT
  originating_timestamp,
  message_text
FROM v$diag_alert_ext
WHERE originating_timestamp > SYSDATE - 7
  AND (message_text LIKE '%trace event%'
       OR message_text LIKE '%ORA-00017%')
ORDER BY originating_timestamp DESC
FETCH FIRST 50 ROWS ONLY;

-- Count occurrences by day to identify patterns
SELECT
  TRUNC(originating_timestamp) AS alert_day,
  COUNT(*) AS occurrence_count
FROM v$diag_alert_ext
WHERE originating_timestamp > SYSDATE - 30
  AND message_text LIKE '%ORA-00017%'
GROUP BY TRUNC(originating_timestamp)
ORDER BY alert_day DESC;

Review Current System-Level Trace Settings

-- Check system-level event settings
SELECT
  name,
  value,
  description
FROM v$parameter
WHERE name IN (
  'event',
  'sql_trace',
  'max_dump_file_size',
  'diagnostic_dest',
  'tracefile_identifier'
)
ORDER BY name;

-- Check for active ORADEBUG sessions
SELECT
  p.spid        AS os_pid,
  s.sid,
  s.serial#,
  s.username,
  s.program,
  s.status
FROM v$process p
JOIN v$session s ON p.addr = s.paddr
WHERE s.username IS NOT NULL
ORDER BY p.spid;

Step-by-Step Resolution

1. Confirm This Is Not a Real Error

ORA-00017 in most contexts is informational, not a blocking error. Before taking any action, confirm the context:

-- Check if any session is actively waiting or blocked
SELECT
  sid,
  serial#,
  username,
  status,
  wait_class,
  event,
  seconds_in_wait
FROM v$session
WHERE status = 'ACTIVE'
  AND wait_class != 'Idle'
ORDER BY seconds_in_wait DESC;

-- Verify no sessions are in error state
SELECT
  sid,
  serial#,
  username,
  last_call_et,
  status,
  sql_id
FROM v$session
WHERE username IS NOT NULL
  AND status = 'INACTIVE'
  AND last_call_et > 3600
ORDER BY last_call_et DESC;

2. Disable Tracing on a Specific Session

If ORA-00017 is flooding the alert log because tracing was left enabled on a session:

-- Disable SQL trace on a specific session
EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => &sid, serial_num => &serial);

-- Alternative: use ALTER SESSION directly in the target session
ALTER SESSION SET SQL_TRACE = FALSE;

-- Disable all events on a specific session using DBMS_SYSTEM
EXEC SYS.DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(
  sid    => &sid,
  serial => &serial,
  sql_trace => FALSE
);

-- Remove specific event on a session
ALTER SYSTEM SET EVENTS 'immediate trace name flush_cache';

3. Disable System-Wide Trace Events

If tracing was set at the system level and needs to be cleared:

-- Turn off system-level SQL trace
ALTER SYSTEM SET SQL_TRACE = FALSE;

-- Clear a specific system event (e.g., 10046)
ALTER SYSTEM SET EVENTS '10046 trace name context off';

-- Clear optimizer trace event
ALTER SYSTEM SET EVENTS '10053 trace name context off';

-- Verify the event parameter is clear
SHOW PARAMETER event;

4. Manage Trace Files to Prevent Disk Space Issues

When tracing is active, trace files can grow rapidly and fill the diagnostic destination:

-- Check diagnostic destination disk usage
SELECT
  name,
  value
FROM v$diag_info
WHERE name IN ('Diag Trace', 'ADR Base', 'ADR Home');

-- Check current max dump file size
SHOW PARAMETER max_dump_file_size;

-- Limit trace file size to prevent runaway growth (in OS blocks or KB)
ALTER SYSTEM SET max_dump_file_size = '100M' SCOPE=BOTH;

-- For a specific session
ALTER SESSION SET max_dump_file_size = '50M';

5. Controlled Tracing with ORADEBUG

When Support or advanced diagnostics require ORADEBUG tracing, follow this controlled approach:

-- Connect as SYSDBA
CONNECT / AS SYSDBA

-- Attach to a specific OS process
ORADEBUG SETOSPID <os_pid>;

-- Or attach by Oracle SID
ORADEBUG SETORAPID <oracle_pid>;

-- Enable extended SQL trace (level 12 = waits + binds)
ORADEBUG EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12;

-- Confirm trace file location
ORADEBUG TRACEFILE_NAME;

-- Disable when done
ORADEBUG EVENT 10046 TRACE NAME CONTEXT OFF;

-- Detach
ORADEBUG DETACH;

6. Using DBMS_MONITOR for Controlled Tracing

The preferred modern approach for session tracing uses DBMS_MONITOR:

-- Enable tracing for a specific session with waits and binds
EXEC DBMS_MONITOR.SESSION_TRACE_ENABLE(
  session_id  => 145,
  serial_num  => 23456,
  waits       => TRUE,
  binds       => TRUE
);

-- Enable tracing for a specific service, module, and action
EXEC DBMS_MONITOR.SERV_MOD_ACT_TRACE_ENABLE(
  service_name => 'ORCL',
  module_name  => 'MY_APP_MODULE',
  action_name  => 'PROCESS_ORDERS',
  waits        => TRUE,
  binds        => FALSE
);

-- Disable tracing for the service/module/action
EXEC DBMS_MONITOR.SERV_MOD_ACT_TRACE_DISABLE(
  service_name => 'ORCL',
  module_name  => 'MY_APP_MODULE',
  action_name  => 'PROCESS_ORDERS'
);

-- Check currently enabled traces
SELECT
  primary_id    AS service,
  qualifier_id1 AS module,
  qualifier_id2 AS action,
  waits,
  binds,
  instance_name
FROM dba_enabled_traces;

Prevention Strategies

1. Establish a Tracing Policy

-- Create a role for tracing privileges to limit who can initiate traces
CREATE ROLE db_trace_admin;
GRANT ALTER SESSION TO db_trace_admin;
GRANT EXECUTE ON DBMS_MONITOR TO db_trace_admin;
GRANT EXECUTE ON DBMS_SYSTEM TO db_trace_admin;

-- Audit trace-related commands
AUDIT ALTER SYSTEM BY ACCESS;
AUDIT EXECUTE ON DBMS_MONITOR BY ACCESS;

2. Monitor Trace File Disk Usage

-- Create a scheduler job to alert on large trace directories
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name        => 'CHECK_TRACE_DISK_USAGE',
    job_type        => 'PLSQL_BLOCK',
    job_action      => q'[
      DECLARE
        v_dest VARCHAR2(512);
      BEGIN
        SELECT value INTO v_dest
        FROM v$diag_info
        WHERE name = ''Diag Trace'';
        -- Log or alert if space is constrained
        DBMS_OUTPUT.PUT_LINE(''Trace dir: '' || v_dest);
      END;
    ]',
    repeat_interval => 'FREQ=HOURLY',
    enabled         => TRUE
  );
END;
/

3. Set Reasonable Trace File Size Limits

-- Set a system default to prevent runaway trace files
ALTER SYSTEM SET max_dump_file_size = '200M' SCOPE=BOTH;

-- Verify setting persists
SELECT name, value, description
FROM v$parameter
WHERE name = 'max_dump_file_size';

4. Best Practices for Diagnostic Tracing

• Always disable tracing immediately after capturing the diagnostic data needed
• Use DBMS_MONITOR rather than ORADEBUG or ALTER SYSTEM SET EVENTS when possible — it is safer and auditable
• Set max_dump_file_size to a finite value in all production databases
• Review dba_enabled_traces regularly to ensure no orphaned trace sessions exist
• Coordinate with Oracle Support before enabling system-level events on production databases

Diagnostic Scripts

These Oracle Day by Day scripts can help diagnose session and trace-related activity:

• gvsess.sql — Identify active sessions and their current wait events
• health.sql — Database health check including alert log review

Related Errors

• ORA-00028 - Your session has been killed
• ORA-01013 - User requested cancel of current operation
• ORA-00600 - Internal error (often accompanied by trace events)
• ORA-07445 - Exception encountered (also generates trace files)

Emergency Response

Quick Fixes

1. Stop all SQL tracing immediately across the database

   ALTER SYSTEM SET SQL_TRACE = FALSE;
   ALTER SYSTEM SET EVENTS '10046 trace name context off';

2. Identify and disable all active session traces

   -- Find all sessions with tracing enabled
   SELECT sid, serial#, username
   FROM v$session
   WHERE sql_trace = 'ENABLED';
   
   -- Disable individually using DBMS_MONITOR
   EXEC DBMS_MONITOR.SESSION_TRACE_DISABLE(session_id => &sid, serial_num => &serial);

3. Recover disk space from excessive trace files

   -- Use ADRCI to purge old diagnostic files
   -- Run from OS command line:
   -- adrci exec="purge -age 60 -type TRACE"
   -- This purges trace files older than 60 minutes
   
   -- From within SQL*Plus, check the trace location
   SELECT value FROM v$diag_info WHERE name = 'Diag Trace';

Post-Resolution Cleanup

-- Audit who enabled tracing
SELECT
  os_username,
  db_username,
  timestamp,
  action_name,
  obj_name
FROM dba_audit_trail
WHERE action_name IN ('ALTER SESSION', 'ALTER SYSTEM')
  AND timestamp > SYSDATE - 1
ORDER BY timestamp DESC;

-- Confirm no traces remain active
SELECT * FROM dba_enabled_traces;

-- Verify max_dump_file_size is set
SHOW PARAMETER max_dump_file_size;