Oracle 定位沒有使用綁定變量的SQL

[[email protected] ~]$ export ORACLE_SID=drb

[[email protected] ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 10.2.0.3.0 - Production on Sat Sep 25 12:27:49 2010

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> create table t1 as select sql_text from v$sqlarea;

Table created.

SQL> alter table t1 add sql_text_wo_constants varchar2(1000);

Table altered.

SQL> create or replace function

2 remove_constants( p_query in varchar2 ) return varchar2

3 as

4 l_query long;

5 l_char varchar2(1);

6 l_in_quotes boolean default FALSE;

7 begin

8 for i in 1 .. length( p_query )

9 loop

10 l_char := substr(p_query,i,1);

11 if ( l_char = '''' and l_in_quotes )

12 then

13 l_in_quotes := FALSE;

14 elsif ( l_char = '''' and NOT l_in_quotes )

15 then

16 l_in_quotes := TRUE;

17 l_query := l_query || '''#';

18 end if;

19 if ( NOT l_in_quotes ) then

20 l_query := l_query || l_char;

21 end if;

22 end loop;

23 l_query := translate( l_query, '0123456789', '@@@@@@@@@@' );

24 for i in 0 .. 8 loop

25 l_query := replace( l_query, lpad('@',10-i,'@'), '@' );

26 l_query := replace( l_query, lpad(' ',10-i,' '), ' ' );

27 end loop;

28 return upper(l_query);

29 end;

30 /

Function created.

SQL> update t1 set sql_text_wo_constants = remove_constants(sql_text);

4569 rows updated.

SQL> commit;

Commit complete.

SQL> select sql_text_wo_constants, count(*) from t1

2 group by sql_text_wo_constants having count(*) > 100 order by 2

3 /

SQL_TEXT_WO_CONSTANTS

--------------------------------------------------------------------------------

COUNT(*)

----------

DELETE FROM MC$DR_BACKUPPIECE_DATAFILE WHERE BSET_KEY=@

1385

DELETE FROM MC$DR_BACKUPPIECE WHERE BSET_KEY=@

1385

DELETE FROM MC$DR_BACKUPSET WHERE BS_ID=@ AND SUB_ID=@ AND DB_KEY=@ AND DB_ID=@

AND BSET_KEY=@

1386

SQL_TEXT_WO_CONSTANTS

--------------------------------------------------------------------------------

COUNT(*)