[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
--//注意开头的. 可不是多余的。
文章来源: 博客园
- 还没有人评论，欢迎说说您的想法！
客服
