获取sql完整脚本，get_fulltext.sh

点击(此处)折叠或打开

1. #!/bin/bash
   
2. # by ray
3. # 2017–08–31
4. #v0.1
6. ##get sqltext from dbs_hist_sqltext,sqltext will be save current directory and file name will be sql_id.txt
7. FromHist(){
8. sqlplus –s /nolog <<–RAY
9.     conn / as sysdba
10.     set linesize 300
11.     set serveroutput on
12.     set feedback off
13.     spool ./$1.txt
14.     declare
15.     l_buffer varchar2(3000);
16.     l_amount binary_integer :=3000;
17.     l_pos int :=1;
18.     l_clob_length int;
19.     sqlid varchar2(100) := ‘$1’;
20.     begin
21.     select DBMS_LOB.getlength(sql_text) into l_clob_length from dba_hist_sqltext where sql_id=sqlid;
22.     while l_pos<l_clob_length loop
23.     select DBMS_LOB.SUBSTR(sql_text,l_amount,l_pos) into l_buffer from dba_hist_sqltext where sql_id=sqlid;
24.     dbms_output.put(l_buffer);
25.     l_pos:=l_pos+l_amount;
26.     end loop;
27.     dbms_output.put_line(‘ ‘);
28.     end;
29.     /
30.     spool off
31.     exit
32. RAY
33. }
36. ##get sqltext from V$sqlarea,sqltext will be save current directory and file name will be sql_id.txt
37. FromMomery(){
38. sqlplus –s /nolog <<–RAY
39.     conn / as sysdba
40.     set linesize 300
41.     set serveroutput on
42.     set feedback off
43.     spool ./$1.txt
44.     declare
45.     l_buffer varchar2(3000);
46.     l_amount binary_integer :=3000;
47.     l_pos int :=1;
48.     l_clob_length int;
49.     sqlid varchar2(100) := ‘$1’;
50.     begin
51.     select DBMS_LOB.getlength(sql_fulltext) into l_clob_length from v\$sqlarea where sql_id=sqlid;
52.     while l_pos<l_clob_length loop
53.     select DBMS_LOB.SUBSTR(sql_fulltext,l_amount,l_pos) into l_buffer from v\$sqlarea where sql_id=sqlid;
54.     dbms_output.put(l_buffer);
55.     l_pos:=l_pos+l_amount;
56.     end loop;
57.     dbms_output.put_line(‘ ‘);
58.     end;
59.     /
60.     spool off
61.     exit
62. RAY
63. }
65. func_help(){
66.     echo “–from specifying how to get sqltext,memery or hist can be used.default momery!!”
67.     echo “–sqlid specify a sql_id”
68.     echo “for example:”
69.     echo “get_fulltext.sh –from=momery –sqlid=*********”
70. }
72. #get parameter
73. argvs=($@)
74. for i in ${argvs[@]}
75. do
76.         case `echo $i | awk –F ‘=’ ‘{print $1}’ | awk –F ‘–‘ ‘{print $2}’| tr [a–z] [A–Z]` in
77.         FROM)
78.             fm=`echo $i | awk –F ‘=’ ‘{print $2}’ | tr [a–z] [A–Z]`
79.         ;;
80.         SQLID)
81.             sqlid=`echo $i | awk –F ‘=’ ‘{print $2}’ `
82.         ;;
83.         HELP)
84.             func_help
85.             exit 1
86.         esac
87. done
89. if [ ! ${fm} ]; then
90.     fm=‘MOMERY’
91. fi
92. if [ ! ${sqlid} ]; then
93.     echo “The sql_id must be specified!!”
94.     exit 1
95. fi
97. ##exec script
98. if [ ${fm} == “HIST” ];then
99.     FromHist “${sqlid}”
100. elif [ ${fm} == “MOMERY” ];then
101.     FromMomery “${sqlid}”
102. else
103.     echo “The type of parameter only are HIST or MOMERY!!”
104. fi