Script to Find out / Identify Trace File for the Current or Other Oracle User Session

advertisements

_____________________________________________________________________________________________________________________

In this post I am going to help you to identify the trace file name for the current oracle session. 

Method 1. 

Using v$diag_info table

SQL> set pages 1000 lines 120 
col name for a20
col value for a70
select name, value                             
from v$diag_info
where name = 'Default Trace File'  ; 

NAME       VALUE
-------------------- ------------------------------------------------------------
Default Trace File   /u01/app/oracle/diag/rdbms/cdb/cdb/trace/cdb_ora_3041.trc

 

advertisements

 

Method 2.

Using user_dump_dest

SQL> select param.value || '/' || instance_name || '_ora_' || pro.spid || '.trc'
from   v$parameter param, v$process pro, v$session sess , v$instance
where  param.name = 'user_dump_dest'
and    sess.username = SYS_CONTEXT('USERENV','SESSION_USER')
and    sess.sid = SYS_CONTEXT('USERENV','SID')
and    pro.addr=sess.paddr ; 

PARAM.VALUE||'/'||INSTANCE_NAME||'_ORA_'||PRO.SPID||'.TRC'
--------------------------------------------------------------------------------
/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/log/cdb_ora_3041.trc


Method 3. 
You can set the trace file name using tracefile_identifier 

parameter

More details :- TRACEFILE_IDENTIFIER Parameter

SQL> alter session set tracefile_identifier='MySess';

Session altered.

SQL> set pages 1000 lines 120 
col name for a20
col value for a70
select name, value                             
from v$diag_info
where name = 'Default Trace File'  ;
SQL> SQL> SQL>   2    3  
NAME       VALUE
-------------------- ----------------------------------------------------------------------
Default Trace File   /u01/app/oracle/diag/rdbms/cdb/cdb/trace/cdb_ora_3041_MySess.trc


SQL>  set pages 1000 lines 120 
col name for a20
col value for a70
select name, value                             
from v$diag_info
where name = 'Default Trace File'  ;

NAME       VALUE
-------------------- ----------------------------------------------------------------------
Default Trace File   /u01/app/oracle/diag/rdbms/cdb/cdb/trace/cdb_ora_3041_MySess.trc

The physical file get created when the sql_trace starts. 

SQL> !ls -tlr /u01/app/oracle/diag/rdbms/cdb/cdb/trace/cdb_ora_3041_MySess.trc
ls: cannot access /u01/app/oracle/diag/rdbms/cdb/cdb/trace/cdb_ora_3041_MySess.trc: No such file or directory

SQL> alter session set sql_trace=true; 

Session altered.

SQL> !ls -tlr /u01/app/oracle/diag/rdbms/cdb/cdb/trace/cdb_ora_3041_MySess.trc
ls: cannot access /u01/app/oracle/diag/rdbms/cdb/cdb/trace/cdb_ora_3041_MySess.trc: No such file or directory

SQL> 
SQL> select name from v$database; 

NAME
--------------------
CDB

SQL> select 1 from dual; 

  1
----------
  1

SQL> !ls -tlr /u01/app/oracle/diag/rdbms/cdb/cdb/trace/cdb_ora_3041_MySess.trc
-rw-r-----. 1 oracle oinstall 2927 Jun 29 06:27 /u01/app/oracle/diag/rdbms/cdb/cdb/trace/cdb_ora_3041_MySess.trc

_____________________________________________________________________________________________________________________

Website Stats