[20220323]完善tpt get_trace.sql指令碼.txt

[20220323]完善tpt get_trace.sql指令碼.txt

--//估計從12c開始，可以不需要登入伺服器訪問檢視跟蹤檔案。透過v$diag_alert_ext檢視可以檢視alert日誌內容。
--// tpt  的指令碼如下：
$ cat alog.sql
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.

COL originating_timestamp FOR a25
COL message_text FOR a100

SELECT to_char(originating_timestamp, 'yyyy-mm-dd hh24:mi:ss') AS originating_timestamp, message_text--, container_name
FROM v$diag_alert_ext
WHERE component_id = 'rdbms'
AND originating_timestamp BETWEEN &1 AND &2
ORDER BY originating_timestamp;

CLEAR COLUMNS
--//注：我註解了最後的container_name欄位資訊。

--//另外tpt下還有兩個指令碼用來檢視跟蹤檔案內容，實際上它是透過spool寫入本地再檢視。
$ ls -l  get_trace*.sql
-rw-r--r-- 1 oracle oinstall  550 2021-09-17 07:27:56 get_trace2.sql
-rw-r--r-- 1 oracle oinstall 1153 2022-03-16 10:27:45 get_trace.sql

--//原始的get_trace.sql如下：
$ cat get_trace.sql
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.
/*----------------------------------------------------------------------------------------------------------------------------
Usage: get_trace <trace_file_name>
----------------------------------------------------------------------------------------------------------------------------*/

DEFINE trc_file = &1

COL trace_filename FOR A45
COL adr_home FOR A45
SELECT trace_filename, to_char(change_time, 'dd-mm-yyyy hh24:mi:ss') AS change_time, to_char(modify_time, 'dd-mm-yyyy hh24:mi:ss') AS modify_time, adr_home, con_id
FROM gv$diag_trace_file
WHERE lower(trace_filename) LIKE lower('%&trc_file%')
ORDER BY modify_time;

PROMPT
--ACCEPT trc_file PROMPT 'Trace file name: '
PROMPT Getting trace file ...
SET HEAD OFF
SET FEEDBACK OFF
SET TERM OFF
@get_trace2 &trc_file
SET HEAD ON
SET FEEDBACK ON
SET TERM ON

--on Mac
host &_start $TMPDIR/&trc_file
--on Windows
--host start %TEMP%/&trc_file
PAUSE
host &_delete $TMPDIR/&trc_file
--on Windows
--host &_delete %TEMP%/&trc_file

--//指令碼有1個缺點就是輸入引數後，實際上還有在執行一次ACCEPT，有一點點多餘。可以直接註解掉ACCEPT。
--//還有1個缺點一般透過ti.sql指令碼獲得的是跟蹤檔案的全路徑，例子：

SCOTT@book> @ ti
New tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_49542_0001.trc

--//get_trace.sql 指令碼只能輸入book_ora_49542_0001.trc,不利於操作
--//一般雙擊選中的是/u01/app/oracle/diag/rdbms/book/book/trace/book_ora_49542_0001.trc
--//如果指令碼支援輸入全路徑就更加合適自己的工作。
--//另外因為tpt指令碼登入記錄跟蹤檔名在TRC變數中，可以透過''空代替。還有指令碼名字太長。我建立了一個軟連結
$ ls -l gt.sql
lrwxrwxrwx 1 oracle oinstall 13 2022-03-24 09:55:37 gt.sql -> get_trace.sql

--//例子如下：
@gt &trc
@gt ''
@gt /u01/app/oracle/diag/rdbms/xxxxx/xxxxx1/trace/xxxxx1_ora_12474.trc

$ cat get_trace.sql
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.

/*----------------------------------------------------------------------------------------------------------------------------
Usage: get_trace <trace_file_name>
----------------------------------------------------------------------------------------------------------------------------*/

--SET TERM OFF
COLUMN trc_file  NEW_VALUE v_trc_file
--DEFINE trc_file = &1
SELECT NVL('&1','&TRC') trc_file FROM DUAL ;
SELECT SUBSTR ('&v_trc_file', INSTR ('&v_trc_file', '/', -1) + 1) trc_file FROM DUAL;
SET TERM ON

DEFINE trc_file = &v_trc_file
--DEFINE trc_file = &1

COL trace_filename FOR A45
COL adr_home FOR A45
SELECT trace_filename, to_char(change_time, 'dd-mm-yyyy hh24:mi:ss') AS change_time, to_char(modify_time, 'dd-mm-yyyy hh24:mi:ss') AS modify_time, adr_home, con_id
FROM gv$diag_trace_file
WHERE lower(trace_filename) LIKE lower('%&v_trc_file%')
ORDER BY modify_time;

PROMPT
--ACCEPT trc_file PROMPT 'Trace file name: '
PROMPT Getting trace file ...
SET HEAD OFF
SET FEEDBACK OFF
SET TERM OFF
@get_trace2 &trc_file
SET HEAD ON
SET FEEDBACK ON
SET TERM ON

--on Mac
host &_start $TMPDIR/&trc_file
--on Windows
--host start %TEMP%/&trc_file
PAUSE
host &_delete $TMPDIR/&trc_file
--on Windows
--host &_delete %TEMP%/&trc_file