Get the trace file name and read it
Posted by Sriram Sanka on December 10, 2010
SQL> COL trace_file FOR A75
SQL> col VALUE format a50
SQL> col name format a20
SQL> select name,value
2 from v$parameter
3 where name='user_dump_dest';
NAME VALUE
-------------------- --------------------------------------------------
user_dump_dest D:\ORACLE\PRODUCT\10.2.0\ADMIN\SRIRAM_TEST\UDUMP
SQL> SELECT s.sid,
s.serial#,
pa.value || '\' || LOWER(SYS_CONTEXT('userenv','instance_name')) ||
'_ora_' || p.spid || '.trc' AS trace_file
FROM v$session s,
v$process p,
v$parameter pa
WHERE pa.name = 'user_dump_dest'
AND s.paddr = p.addr
AND s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
SID SERIAL#
---------- ----------
TRACE_FILE
---------------------------------
143 573 D:\ORACLE\PRODUCT\10.2.0\ADMIN\SRIRAM_TEST\UDUMP\sriramtest_ora_2280.trc
SQL> alter database backup controlfile to trace;
Database altered.
---You can read the file on windows using the below command
SQL> host notepad D:\ORACLE\PRODUCT\10.2.0\ADMIN\SRIRAM_TEST\UDUMP\sriramtest_ora_2280.trc
Not Just Databases 
Leave a comment