Oracle MA SQL

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;