Setting The Event 10116

How to set event 10116?

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.

Read Also
Creating a New Database Using MySql

Have a Oracle Question
Do you have an Oracle Question?

Oracle Books
Oracle Certification, Database Administration, SQL, Application, Programming Reference Books

Oracle Application
Oracle Application Hints and Tips

Oracle Home
Oracle Database, SQL, Application, Programming Tips

All the site contents are Copyright © www.erpgreat.com and the content authors. All rights reserved.
All product names are trademarks of their respective companies.
The site www.erpgreat.com is not affiliated with or endorsed by any company listed at this site.
Every effort is made to ensure the content integrity.  Information used on this site is at your own risk.
 The content on this site may not be reproduced or redistributed without the express written permission of
www.erpgreat.com or the content authors.