We can enable sql trace in various ways.. Here are a few
Credits to :
https://dbaclass.com/article/tracing-sessions-in-oracle/
1. Enabling tracing for all session of a user.
For this we need to create a trigger.
CREATE OR REPLACE TRIGGER USER_TRACING_SESSION
AFTER LOGON ON DATABASE
BEGIN
IF USER = 'SIEBEL'THEN
execute immediate 'alter session set events ''10046 trace name context forever, level 12''';
END IF;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/
2. Enabling trace for a single session(using dbms_system)
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE)
---To disable
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);
--- Get the tracefile name:
SELECT p.tracefile FROM v$session s JOIN v$process p ON s.paddr = p.addr WHERE s.sid = 123;
TRACEFILE
------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/db11g/db11g/trace/db11g_ora_9699.trc
-- Use tkprof to generate readable file
tkprof /u01/app/oracle/diag/rdbms/db11g/db11g/trace/db11g_ora_9699.trc trace_output.txt
3. Enabling trace using oradebug.
--Get the spid from sid.
SELECT p.spid FROM gv$session s JOIN gv$process p ON p.addr = s.paddr AND p.inst_id = s.inst_id and s.sid=1105;
SPID
-----------------
3248
--- Enable tracing for that spid
SQL> oradebug setospid 3248
Oracle pid: 92, Unix process pid: 3248, image: oracle@sec58-6
SQL> oradebug EVENT 10046 trace name context forever, level 12
Statement processed.
-- Find the trace file name
SQL> oradebug TRACEFILE_NAME
/oracle/app/oracle/diag/rdbms/b2crmd2/B2CRMD2/trace/B2CRMD2_ora_3248.trc
-- Disabling trace:
SQL> oradebug setospid 3248
Oracle pid: 92, Unix process pid: 3248, image: oracle@sec58-6
SQL> oradebug event 10046 trace name context off
Statement processed.
4. 10053 trace:
10053 trace is is known as optimizer trace. Below are steps generating 10053 trace for a sql statement.
Note: To generate 10053 trace, we need to hard parse the query, So flush the sql statement from shared pool .
--- set tracefile name
SQL>alter session set tracefile_identifier='TESTOBJ_TRC';
Session altered.
SQL>alter session set events '10053 trace name context forever ,level 1';
Session altered.
-- hard parse the statement
SQL>Select count(*) from TEST_OBJ;
COUNT(*)
----------
33091072
exit
-- trace file name:
/u01/app/oracle/admin/BBCRMST1/diag/rdbms/bbcrmst1/BBCRMST1/trace/BBCRMST1_ora_9046_TESTOBJ_TRC.trc
Alternatively you can generate the 10053 trace, without executing or without hardparsing the sql statement using DBMS_SQLDIAG
suppose sql_id = dmx08r6ayx800
output trace_file=TEST_OBJ3_TRC
begin
dbms_sqldiag.dump_trace(p_sql_id=>'dmx08r6ayx800',
p_child_number=>0,
p_component=>'Compiler',
p_file_id=>'TEST_OBJ3_TRC');
END;
/
-- Trace file
-bash-4.1$ ls -ltr BBCRMST1_ora_27439_TEST_OBJ3_TRC.trc
-rw-r----- 1 oracle oinstall 394822 Jun 30 14:17 BBCRMST1_ora_27439_TEST_OBJ3_TRC.trc
Start session trace
To start a SQL trace for the current session, execute:
ALTER SESSION SET sql_trace = true;
You can also add an identifier to the trace file name for later identification:
ALTER SESSION SET sql_trace = true;
ALTER SESSION SET tracefile_identifier = mysqltrace;
[edit]Stop session trace
To stop SQL tracing for the current session, execute:
ALTER SESSION SET sql_trace = false;
[edit]Tracing other user's sessions
DBA's can use DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION to trace problematic database sessions. Steps:
- Get the SID and SERIAL# for the process you want to trace.
SQL> select sid, serial# from sys.v_$session where ...
SID SERIAL#
---------- ----------
8 13607
- Enable tracing for your selected process:
SQL> ALTER SYSTEM SET timed_statistics = true;
SQL> execute dbms_system.set_sql_trace_in_session(8, 13607, true);
- Ask user to run just the necessary to demonstrate his problem.
- Disable tracing for your selected process:
SQL> execute dbms_system.set_sql_trace_in_session(8,13607, false);
- Look for trace file in USER_DUMP_DEST:
$ cd /app/oracle/admin/oradba/udump
$ ls -ltr
total 8
-rw-r----- 1 oracle dba 2764 Mar 30 12:37 ora_9294.trc
********************************************************************************
Few other useful commands
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>TRUE);
SQL> EXEC DBMS_SYSTEM.set_sql_trace_in_session(sid=>123, serial#=>1234, sql_trace=>FALSE);
SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>8, nm=>' ');
SQL> EXEC DBMS_SYSTEM.set_ev(si=>123, se=>1234, ev=>10046, le=>0, nm=>' ');
Tracing Individual SQL Statements
SQL trace can be initiated for an individual SQL statement by substituting the required SQL_ID into the following statement.
SQL> ALTER SESSION SET EVENTS 'trace[rdbms.SQL_Optimizer.*][sql:sql_id]';
alter system set events '8103 trace name errorstack forever, level 3';
To turn it off
alter system set events '8103 trace name errorstack off, level 3';
If you want to turn at session level( Helpful when you are able to reproduce error when executed from your session)
Please try to reproduce the error as we need a tracefile to investigate. If possible please run the procedures until we see it reproduce.
oradebug setmypid
alter session set max_dump_file_size=unlimited;
alter session set db_file_multiblock_read_count=1;
alter session set events 'immediate trace name trace_buffer_on level 1048576';
alter session set events '10200 trace name context forever, level 1';
alter session set events '8103 trace name errorstack level 3';
alter session set events '10236 trace name context forever, level 1';
alter session set tracefile_identifier='ORA8103';
run the query that produces the error ORA-8103
alter session set events 'immediate trace name trace_buffer_off';
oradebug tracefile_name;
exit
When an error is reported please upload the tracefile identified above by oradebug tracefile_name
Step 3:- Please upload your /var/adm/messages file
