这个也是Tom大神写的工具。
https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1163635055580
下面模拟没有绑定变量的环境,然后使用该工具查看
创建实验帐号
conn / as sysdba
create user edmond identified by edmond;
grant connect,resource,dba to edmond;
create table t(x int);
insert into t select rownum from dual connect by level<=10000;
create or replace procedure p1
as
l_cnt number;
begin
for i in 1 .. 10000
loop
execute immediate 'select count(*) from t where x=' || i into l_cnt;
end loop;
end;
/
在一个终端执行p1过程,开启另一个终端执行如下脚本:
set linesize 200;
col sql_text_wo_constants format a100;
可以看到结果如下,已经找到了没有绑定变量的语句。
https://asktom.oracle.com/pls/asktom/f?p=100:11:::::P11_QUESTION_ID:1163635055580
下面模拟没有绑定变量的环境,然后使用该工具查看
创建实验帐号
conn / as sysdba
create user edmond identified by edmond;
grant connect,resource,dba to edmond;
create table t(x int);
insert into t select rownum from dual connect by level<=10000;
create or replace procedure p1
as
l_cnt number;
begin
for i in 1 .. 10000
loop
execute immediate 'select count(*) from t where x=' || i into l_cnt;
end loop;
end;
/
在一个终端执行p1过程,开启另一个终端执行如下脚本:
- create table t1 as select sql_text from v$sqlarea;
-
- alter table t1 add sql_text_wo_constants varchar2(1000);
-
- create or replace function
- remove_constants( p_query in varchar2 ) return varchar2
- as
- l_query long;
- l_char varchar2(1);
- l_in_quotes boolean default FALSE;
- begin
- for i in 1 .. length( p_query )
- loop
- l_char := substr(p_query,i,1);
- if ( l_char = '''' and l_in_quotes )
- then
- l_in_quotes := FALSE;
- elsif ( l_char = '''' and NOT l_in_quotes )
- then
- l_in_quotes := TRUE;
- l_query := l_query || '''#';
- end if;
- if ( NOT l_in_quotes ) then
- l_query := l_query || l_char;
- end if;
- end loop;
- l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );
- for i in 0 .. 8 loop
- l_query := replace( l_query, lpad('@',10-i,'@'), '@' );
- l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );
- end loop;
- return upper(l_query);
- end;
- /
- update t1 set sql_text_wo_constants = remove_constants(sql_text);
set linesize 200;
col sql_text_wo_constants format a100;
- select sql_text_wo_constants, count(*)
- from t1
- group by sql_text_wo_constants
- having count(*) > 100
- order by 2
- /
可以看到结果如下,已经找到了没有绑定变量的语句。
