Answer:
start tracing from this session
Alter session set SQL_Trace = true ;
ALTER SESSION SET EVENTS 10046 TRACE NAME CONTEXT
FOREVER, LEVEL 8';
ALTER SESSION SET EVENTS 10053 TRACE NAME CONTEXT
FOREVER, LEVEL 1';
also these can be used for tracing
execute DBMS_SESSION.SET_SQL_TRACE ({true|false});
execute DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION (session_id,
serial_id, {true|false});
execute DBMS_SYSTEM.SET_EV(session_id, serial_id, 10046,
level, );
for more information; http://www.ubtools.com/ubtools/products/itrprof/itrprof_user_manual.html
Run the application that you want to trace, any SQL(s)
or any PL/SQL block(s)
select sysdate, user from dual;
stop tracing
Alter session set SQL_Trace = false ;
ALTER SESSION SET EVENTS 10046 TRACE NAME CONTEXT
OFF;
ALTER SESSION SET EVENTS 10053 TRACE NAME CONTEXT
OFF;
Than go to USER_DUMP_DEST to pick up the trace file.
A query that gives your trace file name generated from your session
in windows without the tracefile_identifier would be
as follows
select c.value || \ || d.instance_name || _ora_
|| a.spid || .trc trace_file_is_here
from v$process a, v$session b, v$parameter c, v$instance
d
where a.addr = b.paddr
and b.audsid = userenv(sessionid)
and c.name = user_dump_dest ;
Format the trace file with TKPROF and interpret the
output.
$ tkprof tracefile.trc output.txt [set the options
as you like]
tkprof D:\oraclexee\app\oracle\admin\XE\udump\xe_ora_2220_bind.trc
D:\temp\xe_ora_2220_bind.txt explain=hr/hr sys=yes waits=yes sort=prscpu
Note:
If you want to modify the spfile for the next startup:
SQL> alter system set event='10116 trace name context forever' scope = spfile;
System altered.
If you want to modify the event in memory:
SQL> alter system set events '10116 trace name context forever';
System altered.
