跟踪文件查找

-- 需要查找跟踪文件时，发现目录里面一推文件，不知道需要哪一个，查找资料总结一下,方便以后使用

-- 用户有访问 v$process\v$session\v$parameter\v$instance 表权限时

SELECT c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace
  FROM v$process a, v$session b, v$parameter c, v$instance d
 WHERE a.addr = b.paddr
   AND b.audsid = userenv('sessionid')
   AND c.name = 'user_dump_dest';

点击(此处)折叠或打开

1. EODA@BLACKBOX> select c.value || '/' || d.instance_name || '_ora_' || a.spid || '.trc' trace
   
2.   2 from v$process a, v$session b, v$parameter c, v$instance d
   
3.   3 where a.addr = b.paddr
   
4.   4 and b.audsid = userenv('sessionid')
   
5.   5 and c.name = 'user_dump_dest';

TRACE
----------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/blackbox/BLACKBOX/trace/BLACKBOX_ora_5194.trc

-- Oracle 9i R1

SELECT c.value || '/' || 'ora_' || a.spid || '.trc' trace
  FROM v$process a, v$session b, v$parameter c, v$instance d
 WHERE a.addr = b.paddr
   AND b.audsid = userenv('sessionid')
   AND c.name = 'user_dump_dest';

点击(此处)折叠或打开

1. EODA@BLACKBOX> SELECT c.value || '/' || 'ora_' || a.spid || '.trc' trace
   
2.   2 FROM v$process a, v$session b, v$parameter c, v$instance d
   
3.   3 WHERE a.addr = b.paddr
   
4.   4 AND b.audsid = userenv('sessionid')
   
5.   5 AND c.name = 'user_dump_dest';
   
6. 
   
7. TRACE
   
8. ----------------------------------------------------------------------------------------------------
   
9. /u01/app/oracle/diag/rdbms/blackbox/BLACKBOX/trace/ora_5194.trc

-- 当前会话没有权限访问 v$ 表时
-- 首先，需要用户要有执行 DBMS_MONITOR 包的权限，需授予用户权限

grant execute on dbms_monitor to scott;

-- 直接使用包会报错

点击(此处)折叠或打开

1. SCOTT@BLACKBOX> exec dbms_monitor.session_trace_enable
   
2. BEGIN dbms_monitor.session_trace_enable; END;
   
3. 
   
4.       *
   
5. ERROR at line 1:
   
6. ORA-06550: line 1, column 7:
   
7. PLS-00201: identifier 'DBMS_MONITOR' must be declared
   
8. ORA-06550: line 1, column 7:
   
9. PL/SQL: Statement ignored

-- 授权之后需要修改参数 tracefile_identifier 的值
-- scott 用户为例
alter session set tracefile_identifier = 'TEXT';

点击(此处)折叠或打开

1. SCOTT@BLACKBOX> alter session set tracefile_identifier = 'SCOTT';
   
2. 
   
3. Session altered.

-- 运行 dbms_monitor 包打开会话跟踪，然后查看trace目录下的文件，可以看到当前会话生成的trace文件
exec dbms_monitor.session_trace_enable

点击(此处)折叠或打开

1. SCOTT@BLACKBOX> exec dbms_monitor.session_trace_enable
   
2. 
   
3. PL/SQL procedure successfully completed.

-- 可以轻松的找到当前会话的跟踪文件

点击(此处)折叠或打开

1. SCOTT@BLACKBOX> !ls /$ORACLE_BASE/diag/rdbms/blackbox/$ORACLE_SID/trace/*SCOTT*
2. //u01/app/oracle/diag/rdbms/blackbox/BLACKBOX/trace/BLACKBOX_ora_6046_SCOTT.trc
3. //u01/app/oracle/diag/rdbms/blackbox/BLACKBOX/trace/BLACKBOX_ora_6046_SCOTT.trm
   

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31369239/viewspace-2122981/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31369239/viewspace-2122981/