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
Also 11g onwards, tracefile name is available directly from v$process (column tracefile) and can be viewed from sqlplus (using host vi or host notepad commands)…
TRACEFILE VARCHAR2(513) Trace file name of the process
As explained by you was Host command.
That might not work on Windows.