[ORACLE]获取当前会话的跟踪文件路径
获取当前会话的跟踪文件路径
DUMP path
SELECT d.VALUE || '/' || LOWER (RTRIM (i.INSTANCE, CHR (0))) || '_ora_' || p.spid || '.trc' trace_file_name FROM (SELECT p.spid FROM v$mystat m, v$session s, v$process p WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p, ----获取当前会话的系统进程id (SELECT t.INSTANCE FROM v$thread t, v$parameter v WHERE v.NAME = 'thread' AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i, ----->获取实例名 (SELECT VALUE FROM v$parameter WHERE NAME = 'user_dump_dest') d ; ---->获取DUMP的主路径
trace path
SELECT d.VALUE || '/' || LOWER (RTRIM (i.INSTANCE, CHR (0))) || '_ora_' || p.spid || '.trc' trace_file_name FROM (SELECT p.spid FROM v$mystat m, v$session s, v$process p WHERE m.statistic# = 1 AND s.SID = m.SID AND p.addr = s.paddr) p, ----获取当前会话的系统进程id (SELECT t.INSTANCE FROM v$thread t, v$parameter v WHERE v.NAME = 'thread' AND (v.VALUE = 0 OR t.thread# = TO_NUMBER (v.VALUE))) i, ----->获取实例名 (select value from v$diag_info where name='Diag Trace') d ; ---->获取跟踪文件的主路径
SQL> col value for A60 SQL> select * from v$diag_info; INST_ID NAME VALUE CON_ID ---------- ------------------------- ------------------------------------------------------------ ---------- 1 Diag Enabled TRUE 0 1 ADR Base /oracle/D4C 0 1 ADR Home /oracle/D4C/diag/rdbms/d4cdb/D4C 0 1 Diag Trace /oracle/D4C/diag/rdbms/d4cdb/D4C/trace 0 1 Diag Alert /oracle/D4C/diag/rdbms/d4cdb/D4C/alert 0 1 Diag Incident /oracle/D4C/diag/rdbms/d4cdb/D4C/incident 0 1 Diag Cdump /oracle/D4C/diag/rdbms/d4cdb/D4C/cdump 0 1 Health Monitor /oracle/D4C/diag/rdbms/d4cdb/D4C/hm 0 1 Default Trace File /oracle/D4C/diag/rdbms/d4cdb/D4C/trace/D4C_ora_4244.trc 0 1 Active Problem Count 1 0 1 Active Incident Count 50 0 1 ORACLE_HOME /oracle/D4C/193 0 12 rows selected.
SQL> select tracefile from v$process where addr in (select paddr from v$session where sid in (select sid from v$mystat)); TRACEFILE ------------------------------------------------------------------------------------------------------------------------------------------------------ /oracle/D4C/diag/rdbms/d4cdb/D4C/trace/D4C_ora_10115.trc
v$mystat,v$sesstat是用来分别统计会话级别和自实例起动以来数据库各种统计信息的。两个视图结构一致,只是统计数据不一致。可以说v$mystat是v$sesstat的子集。v$mystat视图中只会有当前用户的会话信息,v$sesstat会有整个实例内所有会话信息
v$statname,v$sysstat视图结构的差别就是v$sysstat比v$statname多一个value列。
查某条DML语句所产生的redo信息。可以这样写:
select value from v$mystat t,v$statname t1 where t.STATISTIC# = t1.STATISTIC# and t1.NAME = 'redo size'
每天进步一点点,多思考,多总结
版权声明:本文为CNblog博主「zaituzhong」的原创文章,遵循 CC 4.0 BY-SA 版权协议,转载请附上原文出处链接及本声明。