[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輸入。
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
--//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
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 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
--//注意開頭的. 可不是多餘的。