In Oracle11g, you can easily locate the current trace file, that is, access the view v $ diag_info.
Store @ TEST> select * from v $ version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
PL/SQL Release 11.2.0.1.0-Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0-Production
NLSRTL Version 11.2.0.1.0-Production
Store @ TEST> set linesize 1500;
Store @ TEST> SELECT * FROM v $ diag_info;
INST_ID NAME VALUE
---------- ---------------------------------------------------------------- Too many threads ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 Diag Enabled TRUE
1 ADR Base/opt/oracle/db
1 ADR Home/opt/oracle/db/diag/rdbms/test/TEST
1 Diag Trace/opt/oracle/db/diag/rdbms/test/TEST/trace
1 Diag Alert/opt/oracle/db/diag/rdbms/test/TEST/alert
1 Diag Incident/opt/oracle/db/diag/rdbms/test/TEST/incident
1 Diag Cdump/opt/oracle/db/diag/rdbms/test/TEST/cdump
1 Health Monitor/opt/oracle/db/diag/rdbms/test/TEST/hm
1 Default Trace File/opt/oracle/db/diag/rdbms/test/TEST/trace/TEST_ora_5555.trc
1 Active Problem Count 0
1 Active Incident Count 0
11 rows have been selected.
The name = 'default Trace file' record in the view is the Default trace File of the current session.
Store @ TEST> SELECT value FROM v $ diag_info WHERE name = 'default Trace file ';
VALUE
Bytes ----------------------------------------------------------------------------------------------------
/Opt/oracle/db/diag/rdbms/test/TEST/trace/TEST_ora_5555.trc
Store @ TEST> alter system set SQL _trace = true;
The system has been changed.
Store @ TEST> select * from dual;
D
-
X
Store @ TEST> alter system set SQL _trace = false;
The system has been changed.
Store @ TEST>! Cat/opt/oracle/db/diag/rdbms/test/TEST/trace/TEST_ora_5555.trc
Trace file/opt/oracle/db/diag/rdbms/test/TEST/trace/TEST_ora_5555.trc
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
ORACLE_HOME =/opt/oracle/db/db_1
System name: Linux
Node name: gtlions
Release: 2.6.18-194. el5PAE
Version: #1 SMP Mon Mar 29 20:19:03 EDT 2010
.....
.....
Parsing in cursor #9 len = 18 dep = 0 uid = 93 oct = 3 lid = 93 tim = 1295493303679484 hv = 942515969 ad = '3a0de6c0 'sqlid = 'a5ks9fhw2v9s1'
Select * from dual
END OF STMT
PARSE #9: c = 14999, e = 158076, p = 0, cr = 3, cu = 0, mis = 1, r = 0, dep = 0, og = 1, plh = 272002086, tim = 1295493303679479
....
....
==================================
Parsing in cursor #7 len = 32 dep = 0 uid = 93 oct = 49 lid = 93 tim = 1295493315875782 hv = 3586475918 ad = '0' sqlid = '19zugf7awajwf'
Alter system set SQL _trace = false
END OF STMT
PARSE #7: c = 0, e = 1849, p = 0, cr = 0, cu = 0, mis = 0, r = 0, dep = 0, og = 0, plh = 0, tim = 1295493315875778
EXEC #7: c = 9999, e = 14897, p = 0, cr = 0, cu = 3, mis = 0, r = 0, dep = 0, og = 0, plh = 0, tim = 1295493315891023
Summary
Key words: v $ diag_info SQL _trace
-The End-