[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