还有 Tom的方法，， 呵呵 很安全。。 MOS的那种方法我曾经提供给客户，结果一运行 ，db hang了。。

tkyte@TKYTE816> create or replace 2 function remove_constants( p_query in varchar2 ) 3 return varchar2 4 as 5 l_query long; 6 l_char varchar2(1); 7 l_in_quotes boolean default FALSE; 8 begin 9 for i in 1 .. length( p_query ) 10 loop 11 l_char := substr(p_query,i,1); 12 if ( l_char = ʹʹʹʹ and l_in_quotes ) 13 then 14 l_in_quotes := FALSE; 15 elsif ( l_char = ʹʹʹʹ and NOT l_in_quotes ) 16 then 17 l_in_quotes := TRUE; 18 l_query := l_query || ʹʹʹ#ʹ; 19 end if; 20 if ( NOT l_in_quotes ) then 21 l_query := l_query || l_char; 22 end if; 23 end loop; 24 l_query := translate( l_query, ʹ0123456789ʹ, ʹ@@@@@@@@@@ʹ ); 25 for i in 0 .. 8 loop 26 l_query := replace( l_query, lpad(ʹ@ʹ,10‐i,ʹ@ʹ), ʹ@ʹ ); 27 l_query := replace( l_query, lpad(ʹ ʹ,10‐i,ʹ ʹ), ʹ ʹ ); 28 end loop; 29 return upper(l_query); 30 end; 31 / Function created.

tkyte@TKYTE816> create global temporary table sql_area_tmp 2 on commit preserve rows 3 as 4 select sql_text, sql_text sql_text_wo_constants 5 from v$sqlarea 6 where 1=0 7 / Table created.

tkyte@TKYTE816> insert into sql_area_tmp (sql_text) 2 select sql_text from v$sqlarea 3 /

tkyte@TKYTE816> update sql_area_tmp 2 set sql_text_wo_constants = remove_constants(sql_text); 3 / 436 rows updated.

tkyte@TKYTE816> select sql_text_wo_constants, count(*) 2 from sql_area_tmp 3 group by sql_text_wo_constants 4 having count(*) > 10 5 order by 2 6 / SQL_TEXT_WO_CONSTANTS COUNT(*) ‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐‐ ‐‐‐‐‐‐‐‐‐‐ INSERT INTO T VALUES ( @) 100