@gettrcname.sql

標籤：des   http   io   ar   sp   檔案   on   art   cti   

http://www.eygle.com/archives/2007/05/script_gettrcname.html

最近有很多朋友問起《深入淺出Oracle》一書中的一個指令碼gettrcname.sql。
這個指令碼用於擷取追蹤檔案的名稱的，可以用於Linux/Unix環境，已經被包含在書的指令碼包中。

其內容如下：
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,
       (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
/

在Linux下執行該指令碼輸入類似：

SQL> @gettrcname

 

TRACE_FILE_NAME
---------------------------------------------------------------------------------------------------
/opt/oracle/admin/eygle/udump/eygle_ora_8415.trc

 

如我們執行一個跟蹤操作：

SQL> alter session set sql_trace=true;

 

Session altered.

SQL> select count(*) from dba_users;

COUNT(*)
----------
9

SQL> ! head /opt/oracle/admin/eygle/udump/eygle_ora_8415.trc
/opt/oracle/admin/eygle/udump/eygle_ora_8415.trc
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production
ORACLE_HOME = /opt/oracle/product/9.2.0
System name: Linux
Node name: jumper.hurray.com.cn
Release: 2.4.21-15.EL
Version: #1 Thu Apr 22 00:27:41 EDT 2004
Machine: i686

 

收錄一下blue_prince提供的更簡化的指令碼:
SELECT    a.VALUE
       || b.symbol
       || c.instance_name
       || ‘_ora_‘
       || d.spid
       || ‘.trc‘ trace_file
  FROM (SELECT VALUE
          FROM v$parameter
         WHERE NAME = ‘user_dump_dest‘) a,
       (SELECT SUBSTR (VALUE, -6, 1) symbol
          FROM v$parameter
         WHERE NAME = ‘user_dump_dest‘) b,
       (SELECT instance_name
          FROM v$instance) c,
       (SELECT spid
          FROM v$session s, v$process p, v$mystat m
         WHERE s.paddr = p.addr AND s.SID = m.SID AND m.statistic# = 0) d
/

簡單解釋一下，供我的讀者們參考。

-The End-

@gettrcname.sql