Find trace file in Oracle

The following script returns the path to the trace file that the current session writes. It returns the path whether or not tracing is enabled.

SQL> SELECT s.sid,
2 s.serial#,
3 pa.value || '\' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||
4 '_ora_' || p.spid || '.trc' AS trace_file
5 FROM v$session s,
6 v$process p,
7 v$parameter pa
8 WHERE pa.name = 'user_dump_dest'
9 AND s.paddr = p.addr
10 AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID')
11 /

SID      SERIAL#    TRACE_FILE
-------  ---------  ------------------------------------------------------------
147      2101       D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCLE\UDUMP\orcl_ora_1000.trc

Check the value for user_dump_dest

NAME             TYPE    VALUE
---------------  ------- --------------------------------------------
user_dump_dest   string  D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCLE\UDUMP

Create a directory

SQL> CREATE DIRECTORY udump_dest AS 'D:\ORACLE\PRODUCT\10.2.0\ADMIN\ORCLE\UDUMP';

Directory created.

Create a function

SQL> CREATE OR REPLACE FUNCTION get_tracefile (file_name VARCHAR2)
2 RETURN VARCHAR2
3 IS
4 dest_loc CLOB;
5 src_loc BFILE;
6 ret VARCHAR2 (4000);
7 BEGIN
8 src_loc := BFILENAME ('UDUMP_DEST', file_name);
9 DBMS_LOB.OPEN (src_loc, DBMS_LOB.lob_readonly);
10 DBMS_LOB.createtemporary (dest_loc, TRUE);
11 DBMS_LOB.loadfromfile (dest_loc, src_loc, 4000);
12 ret := DBMS_LOB.SUBSTR (dest_loc, 4000);
13 DBMS_LOB.CLOSE (src_loc);
14 RETURN ret;
15 END;
16 /

Function created.

Now just the select the trace file name.

SQL> SELECT get_tracefile (‘orcl_ora_1000.trc’) FROM DUAL;
GET_TRACEFILE(‘ORCL_ORA_1000.TRC’)——————————————————————————–Dump file d:\oracle\product\10.2.0\admin\orcle\udump\orcl_ora_1000.trcFri Dec 10 22:54:36 2010ORACLE V10.2.0.1.0 – Production vsnsta=0vsnsql=14 vsnxtr=3Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – ProductionWith the Partitioning, OLAP and Data Mining optionsWindows XP Version V5.1 Service Pack 3CPU : 1 – type 586, 1 Physical CoresProcess Affinity : 0x00000000Memory (Avail/Total): Ph:269M/959M, Ph+PgF:1265M/2314M, VA:1606M/2047MInstance name: orcl
Redo thread mounted by this instance: 1
Oracle process number: 20
Windows thread id: 1000, image: ORACLE.EXE (SHAD)………………….

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: Joel Perez’s Profile

Anuradha’s LinkedIn: Anuradha’s Profile

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp