[20211126]完善tpt pr.sql脚本.txt

[20211126]完善tpt pr.sql脚本.txt --//tpt提供pr.sql脚本把原来横向输出的内容变成纵向输出，便于阅读。但是有一个小问题，通过例子说明： 1.环境： 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 2.测试： SCOTT@book> @ spid        SID    SERIAL# PROCESS                  SERVER    SPID       PID  P_SERIAL# C50 ---------- ---------- ------------------------ --------- ------ ------- ---------- --------------------------------------------------         44       1574 35220                    DEDICATED 35221       27        146 alter system kill session '44,1574' immediate; SCOTT@book> @ usid 44 USERNAME                SID                 AUDSID OSUSER           MACHINE            PROGRAM              SPID             OPID CPID                     SQL_ID         HASH_VALUE   LASTCALL STATUS   SADDR            PADDR            TADDR ----------------------- -------------- ----------- ---------------- ------------------ -------------------- -------------- ------ ------------------------ ------------- ----------- ---------- -------- ---------------- ---------------- ---------------- LOGON_TIME ------------------- SCOTT                    '44,1574'        18290945 oracle           xxxxxxdg4           (TNS V1-V3)          35221              27 35220                    9r6m4c0hpg6dx   559389117          0 ACTIVE   000000008638EC10 000000008620F338 2021-11-26 11:12:29 --//输出内容太多换行了。 SCOTT@book> @ pr ERROR: ORA-01756: quoted string not properly terminated --//出现ora-01756错误。检查pr.sql脚本，发现使用\字符作为分隔。 0 c clob := q'\ 0 declare 999999      \';; $ grep '\\' usid.sql        substr(s.machine,instr(s.machine,'\')) u_machine, --//而usid脚本里面正好也有字符\.这样导致分隔报错。这样修改pr.sql选择一个不常用的字符作为分隔就ok了。选择那个呢？ --//实际上任何可见的字符都可能出现问题，很简单选择一个不可见字符ctrl+g(小喇叭发生声音，一般代码不会出现)或者ctrl+f作为分隔. --//在linux 的vim下通过ctrl+v ctrl+f输入。 SCOTT@book> @ pr PL/SQL procedure successfully completed. --//嗯，没有输出。查看代码可以发现usid.sql脚本里面有&1要替换，修改如下执行： SCOTT@book> @ pr 44 ============================== U_USERNAME                    : SCOTT U_SID                         :  '44,1574' U_AUDSID                      : 18290945 U_OSUSER                      : oracle U_MACHINE                     : xxxxxxdg4 U_PROGRAM                     : (TNS V1-V3) U_SPID                        : 35221 U_PID                         : 27 CPID                          : 35220 SQL_ID                        : 2jwdj6msnrx81 USID_SQL_HASH_VALUE           : 4048286977 LASTCALL                      : 0 STATUS                        : ACTIVE SADDR                         : 000000008638EC10 PADDR                         : 000000008620F338 TADDR                         : LOGON_TIME                    : 2021-11-26 11:12:29 PL/SQL procedure successfully completed. --//OK。 $ cat -vs pr.sql . -- Notes:   This script is based on Tom Kyte's original printtbl code ( http://asktom.oracle.com ) --          For coding simplicity (read: lazyness) I'm using custom quotation marks ( q'\ ) so --          this script works only from Oracle 10gR2 onwards def _pr_tmpfile=&_tpt_tempdir/pr_&_tpt_tempfile..tmp @@saveset set serverout on size 1000000 termout off save &_pr_tmpfile replace set termout on 0 c clob := q'^F 0 declare 999999      ^F';; 999999      l_theCursor     integer default dbms_sql.open_cursor;; 999999      l_columnValue   varchar2(4000);; 999999      l_status        integer;; 999999      l_descTbl       dbms_sql.desc_tab;; 999999      l_colCnt        number;; 999999  begin 999999      dbms_sql.parse(  l_theCursor, c, dbms_sql.native );; 999999      dbms_sql.describe_columns( l_theCursor, l_colCnt, l_descTbl );; 999999      for i in 1 .. l_colCnt loop 999999          dbms_sql.define_column( l_theCursor, i, 999999                                  l_columnValue, 4000 );; 999999      end loop;; 999999      l_status := dbms_sql.execute(l_theCursor);; 999999      while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop 999999          dbms_output.put_line( '==============================' );; 999999          for i in 1 .. l_colCnt loop 999999                  dbms_sql.column_value( l_theCursor, i, 999999                                         l_columnValue );; 999999                  dbms_output.put_line 999999                      ( rpad( l_descTbl(i).col_name, 999999                        30 ) || ': ' || l_columnValue );; 999999          end loop;; 999999      end loop;; 999999  exception 999999      when others then 999999          dbms_output.put_line(dbms_utility.format_error_backtrace);; 999999          raise;; 999999 end;; / @@loadset get &_pr_tmpfile nolist host &_delete &_pr_tmpfile --//注意开头的. 可不是多余的。