以下介绍2中查看当前用户的trace文件的位置的方法
方法1：标记用户trace文件

SQL> alter session set tracefile_identifier='yallonking';

Session altered.

SQL> set line 200   

打开用户的sql_trace并收集sql执行的信息后关闭sql_trace

SQL> alter session set sql_trace=true;

Session altered.

SQL> select * from scott.emp e where e.deptno in (select deptno from scott.dept) and rownum<3;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30

SQL> alter session set sql_trace=false;

Session altered.

查找用户trace文件

SQL> !ls -ltr /u01/app/oracle/admin/ora10gr2/udump/*yallonking*
-rw-r----- 1 oracle oinstall   70 Feb 21 23:15 /u01/app/oracle/admin/ora10gr2/udump/ora10gr2_ora_9540_yallonking.trc
-rw-r----- 1 oracle oinstall 2003 Feb 21 23:16 /u01/app/oracle/admin/ora10gr2/udump/ora10gr2_ora_9647_yallonking.trc
-rw-r----- 1 oracle oinstall 2146 Feb 21 23:17 /u01/app/oracle/admin/ora10gr2/udump/ora10gr2_ora_9655_yallonking.trc

查看用户trace文件中的执行计划

SQL> !vi /u01/app/oracle/admin/ora10gr2/udump/ora10gr2_ora_9655_yallonking.trc

方法2：使用sql查询用户trace文件

SQL> set line 200 
SQL> alter session set "_optimizer_trace"=all;

Session altered.

SQL> select * from scott.emp e where e.deptno in (select deptno from scott.dept) and rownum<4;

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80        800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30

SQL> alter session set "_optimizer_trace"=none;

Session altered.

查看当前会话的trace文件位置

SQL> SELECT a.VALUE
  2  || b.symbol
  3  || c.instance_name
  4  || '_ora_'
  5  || d.spid
  6  || '.trc' trace_file
  7  FROM (SELECT VALUE
  8  FROM v$parameter
  9  WHERE NAME = 'user_dump_dest') a,
 10  (SELECT SUBSTR (VALUE, -6, 1) symbol
 11  FROM v$parameter
 12  WHERE NAME = 'user_dump_dest') b,
 13  (SELECT instance_name
 14  FROM v$instance) c,
 15  (SELECT spid
 16  FROM v$session s, v$process p, v$mystat m
 17  WHERE s.paddr = p.addr AND s.SID = m.SID AND m.statistic# = 0) d
 18  /

TRACE_FILE
--------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/admin/ora10gr2/udump/ora10gr2_ora_9759.trc