dbms_sql2.expand_sql_text和dbms_utility.expand_sql_text

网上看到lfree大佬写的脚本非常的帅，这里整理下。整合到shell脚本中使用更加便捷,原文地址如下：

http://blog.itpub.net/267265/viewspace-2893172/

一：原SQL脚本

#cat expandz.sql

set long 40000
set serveroutput on
column arg new_value arg

set term off
select decode(&2,11,'sql2','utility') arg from dual;
set term on
prompt
declare
    l_sqltext clob := null;
    l_result  clob := null;
begin
        select sql_fulltext into l_sqltext from v$sqlarea where sql_id='&&1';
        dbms_&arg..expand_sql_text(l_sqltext,l_result);
        dbms_output.put_line(l_result);
end;
/
set serveroutput off

二：整理后的shell脚本

if [ "$DBUSER" = "" ]; then
DBUSER="/ as SYSDBA"
fi

if [ "$1" = "expandz" -o "$1" = "expand" ]; then

if [ "$2" = "" ]; then
echo "ora expandz sql_id version(default 11)"
exit 0
fi

if [ "$3" = "" -o "$3" = 11 ]; then
sqlplus -s /NOLOG << !
connect $DBUSER
set long 40000
set serveroutput on
declare
l_sqltext clob := null;
l_result clob := null;
begin
select sql_fulltext into l_sqltext from v\$sqlarea where sql_id='$2';
dbms_sql2.expand_sql_text(l_sqltext,l_result);
dbms_output.put_line(l_result);
end;
/
set serveroutput off
!
exit 0
fi

if [ "$3" != 11 ]; then
sqlplus -s /NOLOG << !
connect $DBUSER
set long 40000
set serveroutput on
declare
l_sqltext clob := null;
l_result clob := null;
begin
select sql_fulltext into l_sqltext from v\$sqlarea where sql_id='$2';
dbms_utility.expand_sql_text(l_sqltext,l_result);
dbms_output.put_line(l_result);
end;
/
set serveroutput off
!
exit 0
fi
fi

 

三：验证测试

#脚本中默认的版本就是11G，如果输入的数据大于11，就调用dbms_utility.expand_sql_text包体
[oracle@oracle11g ~]$ ora expand 5yv7yvjgjxugg
SELECT "A1"."TIME_WAITED_MICRO" "TIME_WAITED_MICRO" FROM (SELECT "A2"."EVENT"
"EVENT","A2"."TIME_WAITED_MICRO" "TIME_WAITED_MICRO" FROM (SELECT
"A3"."INST_ID" "INST_ID","A3"."KSLEDNAM"
"EVENT","A4"."KSLESTIM_UN"+"A4"."KSLESTIM_FG"+"A4"."KSLESTIM_BG"
"TIME_WAITED_MICRO" FROM SYS."X$KSLEI" "A4",SYS."X$KSLED" "A3" WHERE
("A4"."KSLESWTS_UN">0 OR "A4"."KSLESWTS_FG">0 OR "A4"."KSLESWTS_BG">0) AND
"A4"."KSLESEVT"="A3"."INDX") "A2" WHERE "A2"."INST_ID"=USERENV('INSTANCE')) "A1"
WHERE "A1"."EVENT"='Shared IO Pool Memory'

##19c验证测试

[oracle@db03 ~]$ export ORACLE_PDB_SID=lhh
[oracle@db03 ~]$ sqlplus -v

SQL*Plus: Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

[oracle@db03 ~]$ ora expand f1s77n38c3fj3 19
SELECT COUNT(*) "COUNT(*)" FROM "SYS"."A" "A2","SYS"."B" "A1" WHERE
"A2"."OBJECT_ID"<100 AND "A2"."OBJECT_ID"="A1"."OBJECT_ID"