[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