冷宫

捕获非绑定变量sql（临时淘汰版本1）

create or replace TYPE any_clob_value  AUTHID CURRENT_USER AS OBJECT
(
  CURR_STR clob,
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT any_clob_value) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT any_clob_value,  
  P1 IN clob) RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN any_clob_value,
  RETURNVALUE OUT clob,
  FLAGS IN NUMBER)
  RETURN NUMBER,
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT any_clob_value,  
  SCTX2 IN any_clob_value) RETURN NUMBER
);
/
 
create or replace TYPE BODY any_clob_value
IS
  STATIC FUNCTION ODCIAGGREGATEINITIALIZE(SCTX IN OUT any_clob_value)
  RETURN NUMBER  
  IS
  BEGIN
  SCTX := any_clob_value(NULL) ;
  RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEITERATE(SELF IN OUT any_clob_value,
  P1 IN clob)  
  RETURN NUMBER  
  IS
  BEGIN
  CURR_STR :=  P1;
  RETURN ODCICONST.SUCCESS;
  END;
  
  MEMBER FUNCTION ODCIAGGREGATETERMINATE(SELF IN any_clob_value,
  RETURNVALUE OUT clob,
  FLAGS IN NUMBER)
  RETURN NUMBER  
  IS
  BEGIN
  RETURNVALUE := CURR_STR ;
  RETURN ODCICONST.SUCCESS;
  END;
  MEMBER FUNCTION ODCIAGGREGATEMERGE(SELF IN OUT any_clob_value,  
  SCTX2 IN any_clob_value)  
  RETURN NUMBER  
  IS
  BEGIN
  RETURN ODCICONST.SUCCESS;
  END;
END;
/
 
create or replace FUNCTION anyClobValue(P1 clob) RETURN clob AGGREGATE USING any_clob_value ;

select anyClobValue('3') from dual;


select 
FORCE_MATCHING_SIGNATURE,(select dbms_lob.substr(anyClobValue(SQL_FULLTEXT)) from v$SQL i where i.FORCE_MATCHING_SIGNATURE =o.FORCE_MATCHING_SIGNATURE) max_sql_text,count(1)
from v$sql o
where FORCE_MATCHING_SIGNATURE > 0
and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(1) > 100；

drop type any_clob_value;
drop function anyClobValue;

捕获非绑定变量sql（临时淘汰版本2）

create table SQL_TEMP
(
  sql_id       VARCHAR2(13),
  countnum     NUMBER,
  sql_fulltext CLOB
);
truncate table sql_temp;
insert into sql_temp(SQL_ID,countnum) 
select max(SQL_ID) SQL_ID,count(1) countnum
from v$sql o
where FORCE_MATCHING_SIGNATURE > 0
and FORCE_MATCHING_SIGNATURE != EXACT_MATCHING_SIGNATURE
group by FORCE_MATCHING_SIGNATURE
having count(1) > 100;

update sql_temp o set SQL_FULLTEXT=(select SQL_FULLTEXT from  v$sql i where o.SQL_ID=i.SQL_ID and i.CHILD_NUMBER=0) ;
select countnum,dbms_lob.substr(SQL_FULLTEXT,4000) sqltext from sql_temp l order by countnum;