Sql Trace and Oradebug

SQL> oradebug hanganalyze 3


SQL> oradebug setmypid

SQL> oradebug dump systemstate 10




Identify trace files :
===============

For DBA :

alter session set tracefile_identifier='ODBA';

select tracefile from v$process join v$session on (addr=paddr)  where sys_context('userenv','sessionid') = audsid


For user :

select tracefile from v$process join v$session on (addr=paddr) where sid = <user Sid> ;



Pre 11g :

select u_dump.value || '/' || instance.value || '_ora_' || v$process.spid
|| nvl2(v$process.traceid, '_' || v$process.traceid, null ) || '.trc'"Trace File"
from V$PARAMETER u_dump
cross join V$PARAMETER instance
cross join V$PROCESS
join V$SESSION on v$process.addr = V$SESSION.paddr
where u_dump.name = 'user_dump_dest'
and instance.name = 'instance_name'
and V$SESSION.audsid=sys_context('userenv','sessionid');