[20211130]完善tpt t.sql指令碼.txt

[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
-- 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';