My Experiments with Oracle

By Sriram Sanka -The AUAA

Get the trace file name and read it

Posted by sriram 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,


            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 = 'user_dump_dest'

     AND    s.paddr = p.addr

     AND    s.audsid = SYS_CONTEXT('USERENV', 'SESSIONID');
---------- ----------
  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


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: