My Experiences with Databases

Oracle,MySQL,SQL SERVER,Python,Azure,AWS,Oracle Cloud,GCP Etc

  • Enter your email address to follow this blog and receive notifications of new posts by email.

  • Total Views

    • 315,905 hits
  • $riram $anka


    The experiences, Test cases, views, and opinions etc expressed in this website are my own and does not reflect the views or opinions of my employer. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content.Product and company names mentioned in this website may be the trademarks of their respective owners.

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

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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: