Oracle Tracing

Syntax:

ALTER SESSION SET EVENTS '<event> trace name context forever, level <level>';

Common events

SQL Trace - Event 10046

1 - Same as ALTER SESSION SET sql_trace = TRUE
4 - include bind variables
8 - include wait statistics
12 - include bind variables and wait statistics

ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';

Optimiser Decisions - Event 10053

1 - computations and statistics
2 - computations only

ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';

Other events

Oracle 12c query to list other traceable events:

WITH
  FUNCTION fn_errm(n NUMBER)
  RETURN VARCHAR2
  IS
  BEGIN
    RETURN SQLERRM(n);
  END fn_errm;
error_messages AS (
  SELECT 10000 + rownum AS event, fn_errm(-9999 - rownum) AS description
  FROM   dba_objects
  WHERE  rownum <= 1000
)
SELECT *
FROM   error_messages
WHERE  LOWER(description) LIKE '%trace%';

Potentially more information about an event listed by the above

[rik@o12102nc trace]$ oerr ora 10065
10065, 00000, "limit library cache dump information for state object dump"
// *Document: NO
// *Cause:
// *Action: level 1 - minimal (only the address of state objects)
//          level 2 - little more (no object details)
//          level 3 - normal

Parameters

Parameter	Description
`tracefile_identifier='XXX'`	Add _XXX to tracefile name
`_trace_files_public = TRUE`	Open up trace file for "world" read access (parameter file setting)
`times_statistics = TRUE`	Include timed statistics
`user_dump_dest='<directory>'`	Log file location
`background_dump_dest='<directory>'`	Log file location
`max_dump_file_size=<size>`	Maximum trace file size