oracle对非使用绑定变量的语句去重
通过如下过程可以对非使用绑定变量的语句去重
create or replace procedure query_sql is
cursor fms is select FORCE_MATCHING_SIGNATURE as fms, count(1) as count
from sys.LARGE2S_TABLE
where FORCE_MATCHING_SIGNATURE > 0
and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(1) > 100
order by 2;
v_fms number;
v_sql01 varchar2(3999);
v_sql02 varchar2(3999);
begin
for i in fms loop
v_sql01:='insert into shsnc.long_sql(sql_id,FORCE_MATCHING_SIGNATURE,sql_text) select sql_id,FORCE_MATCHING_SIGNATURE,sql_fulltext from (select sql_id,FORCE_MATCHING_SIGNATURE,sql_fulltext from sys.LARGE2S_TABLE where FORCE_MATCHING_SIGNATURE='||i.fms||' and sql_id is not null and FORCE_MATCHING_SIGNATURE not in (select FORCE_MATCHING_SIGNATURE from shsnc.long_sql)) where rownum<2';
v_sql02:='update shsnc.long_sql set count='||i.count ||' where FORCE_MATCHING_SIGNATURE='||i.fms;
execute immediate v_sql01;
commit;
execute immediate v_sql02;
commit;
end loop;
end;
/
create or replace procedure query_sql is
cursor fms is select FORCE_MATCHING_SIGNATURE as fms, count(1) as count
from sys.LARGE2S_TABLE
where FORCE_MATCHING_SIGNATURE > 0
and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(1) > 100
order by 2;
v_fms number;
v_sql01 varchar2(3999);
v_sql02 varchar2(3999);
begin
for i in fms loop
v_sql01:='insert into shsnc.long_sql(sql_id,FORCE_MATCHING_SIGNATURE,sql_text) select sql_id,FORCE_MATCHING_SIGNATURE,sql_fulltext from (select sql_id,FORCE_MATCHING_SIGNATURE,sql_fulltext from sys.LARGE2S_TABLE where FORCE_MATCHING_SIGNATURE='||i.fms||' and sql_id is not null and FORCE_MATCHING_SIGNATURE not in (select FORCE_MATCHING_SIGNATURE from shsnc.long_sql)) where rownum<2';
v_sql02:='update shsnc.long_sql set count='||i.count ||' where FORCE_MATCHING_SIGNATURE='||i.fms;
execute immediate v_sql01;
commit;
execute immediate v_sql02;
commit;
end loop;
end;
/
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29446986/viewspace-1331287/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
查找数据库里执行大于2s的语句
请登录后发表评论
登录
全部评论
