Alejandro Vargas' Blog

Answering feedback from attendees to the last Open World, the possibility to contribute experiences for the next conference has been opened to the entire Oracle community: Customers, Partners and Employees

If you wish to send a paper for consideration log into this site: OOW 2009 Call for Papers.

The deadline for submission was extended until April 26.

Whenever there is an error condition that do not provide enough information to immediately diagnose the root cause and find a solution, tracing the session that is generating the problem is the best first option to get more information.

In many cases I did find on the raw trace the additional information required to get to the root cause of a problem.

This method is good also to generate high level SQL traces to be used for SQL tuning.

This is a short reference for running oradebug with event 10046 that enable SQL statement tracing including binds and waits.

To trace same session:

oradebug setmypid ;

To trace other session first you need to get it's spid:

select a.username,b.spid
from v$session a, v$process b
where a.username like '%&user%'
and a.paddr=b.addr ;

Then from your session, connected as sys, attach to the other session:

oradebug setospid &ospid ;

Then check the tracefile name:

oradebug tracefile_name ;

At this moment you can start tracing, there are many different events that can be used for different purposes, event 10046 is very useful for debugging, level 12 provide information about what are the waits the session is waiting for and also the values of the bind variables :

oradebug event 10046 trace name context forever, level 12 ;

Check the trace as it run:

! tail

To interrupt the trace you can either exit the session or execute this command:

oradebug event 10046 trace name context off ;

More information about oradebug and its many options can be find on this page "Utiliwiki"