There are multiple methods for enabling tracing for 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

5. Enable trace for a sql id :

alter system set events ‘sql_trace [sql:4dz8h59sdf2gv9]’;