[20211130]完善tpt t.sql脚本.txt

--//最近一直在看tpt脚本，在tpt脚本有一个人脚本用来获取跟踪文件。脚本t.sql，脚本很简单。
$ cat t.sql
SELECT value tracefile FROM v$diag_info WHERE name = 'Default Trace File';

--//实际上脚本仅仅支持11g以上，在10g下无法使用，会报错。看了许多tpt脚本，自己修改看看，完善这部分功能。
--//利用变量的替换功能实现。

$ cat ttt.sql
def trc=unknown
column tracefile noprint new_value trc

define noprint='noprint'
--define noprint=''

set termout off head off

col tpt_version_old  &noprint new_value _tpt_version_old
col tpt_version_new  &noprint new_value _tpt_version_new
col tpt_noprint      &noprint new_value _tpt_noprint

WITH version AS (SELECT TO_NUMBER (SUBSTR (version, 1, 2)) v FROM v$instance)
SELECT CASE WHEN v <= 10 THEN '' ELSE '--' END tpt_version_old
      ,CASE WHEN v > 10  THEN '' ELSE '--' END tpt_version_new
  FROM version;

--set termout on head on

                   SELECT
&&_tpt_version_new value tracefile ,
                   1 tpt_noprint FROM
&&_tpt_version_new v$diag_info,
                   dual  where
&&_tpt_version_new name = 'Default Trace File' and
                   1=1;

                    SELECT
&&_tpt_version_old         value ||'/'||(select instance_name from v$instance) ||'_ora_'||
&&_tpt_version_old         (select spid||case when traceid is not null then '_'||traceid else null end
&&_tpt_version_old                from v$process where addr = (select paddr from v$session
&&_tpt_version_old                                           where sid = (select sid from v$mystat
&&_tpt_version_old                                                      where rownum = 1
&&_tpt_version_old                                                 )
&&_tpt_version_old                                      )
&&_tpt_version_old         ) || '.trc' tracefile,
                    1 tpt_noprint
                    from v$parameter where name = 'user_dump_dest';

set termout on head on

prompt
prompt tracefile_identifier = &trc
prompt
col tracefile print

--//注：实际上以下两句在init.sql有不需要定义，因为登录是自动通过login.sql调用init.sql.
def trc=unknown
column tracefile noprint new_value trc

--//测试：
--//10g的环境：
SCOTT@192.168.100.33:1521/test> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx            10.2.0.4.0     Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

SCOTT@192.168.100.33:1521/test> @ ttt

tracefile_identifier = /u01/app/oracle/admin/test/udump/test_ora_10503.trc

SCOTT@192.168.100.33:1521/test> @ ti

New tracefile_identifier = /u01/app/oracle/admin/test/udump/test_ora_10503.trc
SCOTT@192.168.100.33:1521/test> @ ttt

tracefile_identifier = /u01/app/oracle/admin/test/udump/test_ora_10503_0001.trc

--//注意 ti.sql脚本使用新的方式获取trc文件这样在10g下不对。ti.sql输出结果有错。
--//11g的环境：
SCOTT@book> @ ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

SCOTT@book> @ ttt

tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_63951.trc

SCOTT@book> @ ti

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

tracefile_identifier = /u01/app/oracle/diag/rdbms/book/book/trace/book_ora_63951_0001.trc

--//随便贴上我改写ti.sql脚本：
$ cat ti.sql
-- Copyright 2018 Tanel Poder. All rights reserved. More info at http://tanelpoder.com
-- Licensed under the Apache License, Version 2.0. See LICENSE.txt for terms & conditions.

@@saveset

column _ti_sequence noprint new_value _ti_sequence

set feedback off heading off

select trim(to_char( &_ti_sequence + 1 , '0999' )) "_ti_sequence" from dual;

alter session set tracefile_identifier="&_ti_sequence";

set feedback on heading on

set termout off

column tracefile noprint new_value trc

set termout off head off

define noprint='noprint'
--define noprint=''

col tpt_version_old  &noprint new_value _tpt_version_old
col tpt_version_new  &noprint new_value _tpt_version_new
col tpt_noprint      &noprint new_value _tpt_noprint

WITH version AS (SELECT TO_NUMBER (SUBSTR (version, 1, 2)) v FROM v$instance)
SELECT CASE WHEN v <= 10 THEN '' ELSE '--' END tpt_version_old
      ,CASE WHEN v > 10  THEN '' ELSE '--' END tpt_version_new
  FROM version;

--set termout on head on

                   SELECT
&&_tpt_version_new value tracefile ,
                   1 tpt_noprint FROM
&&_tpt_version_new v$diag_info,
                   dual  where
&&_tpt_version_new name = 'Default Trace File' and
                   1=1;

                    SELECT
&&_tpt_version_old         value ||'/'||(select instance_name from v$instance) ||'_ora_'||
&&_tpt_version_old         (select spid||case when traceid is not null then '_'||traceid else null end
&&_tpt_version_old                from v$process where addr = (select paddr from v$session
&&_tpt_version_old                                           where sid = (select sid from v$mystat
&&_tpt_version_old                                                      where rownum = 1
&&_tpt_version_old                                                 )
&&_tpt_version_old                                      )
&&_tpt_version_old         ) || '.trc' tracefile,
                    1 tpt_noprint
                    from v$parameter where name = 'user_dump_dest';

set termout on head on

-- SELECT value tracefile FROM v$diag_info WHERE name = 'Default Trace File';

-- this is from from old 9i/10g days...
--
--      select value ||'/'||(select instance_name from v$instance) ||'_ora_'||
--             (select spid||case when traceid is not null then '_'||traceid else null end
--                from v$process where addr = (select paddr from v$session
--                                               where sid = (select sid from v$mystat
--                                                          where rownum = 1
--                                                     )
--                                          )
--             ) || '.trc' tracefile
--      from v$parameter where name = 'user_dump_dest';

set termout on
@@loadset

prompt New tracefile_identifier = &trc
prompt
col tracefile print

--//理论讲init.sql也存在问题，我下载的版本还是使用旧的模式，我不想修改了，我建议修改为新的方式，毕竟10g以下版本现在很少
--//人用了。
SELECT value tracefile FROM v$diag_info WHERE name = 'Default Trace File';