Query session trace file directory:
9iR2&10g:
select c.value || '/' || d.instance_name ||
'_ora_' || a.spid || '.trc' trace
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'
/
9iR1:
select c.value || 'ora_' || a.spid || '.trc'
mark trace file:
alter session set tracefile_identifier = '';
alter session set sql_trace=true;
...
alter session set sql_trace=false;
linux check ORA ERROR MSG:
!oerr ora number
LOCK:
SELECT username,v$lock.SID,id1,id2,lmode,request,BLOCK,v$lock.TYPE
FROM v$lock,v$session
WHEREv$lock.SID=v$session.SIDANDv$session.username=USER;
blocking session:
SELECT (SELECTusername
FROMv$session
WHERESID=a.SID)blocker,a.SID,' is blocking ',
(SELECTusername
FROMv$session
WHERESID=b.SID)blockee,b.SID
FROMv$lock a,v$lock b
WHEREa.BLOCK=1ANDb.request>0ANDa.id1=b.id1ANDa.id2=b.id2;
DDL LOCK:
select * from dba_ddl_locks;