Oracle自定义过程来获得完整的sql语句

因为Oracle将sql共享之后，截取出来的sql语句是带变量的

创建一个函数 jy_getsql来获得执行时的完整sql语句


CREATE OR REPLACE FUNCTION jy_getsql (my_sql_id in varchar2)


RETURN clob


IS


Result  clob;




cursor jl(p_sql_id in varchar2) is


  select decode(instr(b.BIND_NAME,'SYS'),0,':'||b.BIND_NAME||'',''||chr(58)||chr(34)||b.BIND_NAME||chr(34)||'')  name,




  decode(b.DATATYPE,2,dbms_sqltune.extract_bind(a.bind_data,b.POSITION).value_string ,''''||dbms_sqltune.extract_bind(a.bind_data,b.POSITION).value_string ||'''')


          value_string


  from v$sqlarea a ,v$sql_bind_metadata b


  where a.LAST_ACTIVE_CHILD_ADDRESS = b.ADDRESS


    and a.SQL_ID = p_sql_id


  order by b.POSITION desc;




BEGIN


 


    select a.SQL_FULLTEXT into Result


    from v$sqlarea a where a.SQL_ID=my_sql_id;


 




    for r in jl(my_sql_id) loop


      Result := replace(Result,r.name,r.value_string);


    end loop;


 




RETURN Result;


 




EXCEPTION


WHEN OTHERS


THEN


RETURN Result;


END;


下面的查询是查询数据库当前正处于等待状态的sql语句


select sw.sid,s.username,sw.event,sw.wait_time,sw.state,sw.seconds_in_wait,p.PROGRAM,s.MACHINE,


(select  c.SQL_FULLTEXT from v$sqlarea c where c.SQL_ID=s.SQL_ID) sql_fulltext,


(select  c.BIND_DATA from v$sqlarea c where c.SQL_ID=s.SQL_ID) BIND_DATA,s.SQL_ID,


jy_getsql(s.SQL_ID)


from v$session s,v$session_wait sw,v$process p


where s.username is not null and s.PADDR=p.ADDR


and sw.sid=s.sid and sw.event not like'%SQL*Net%'


order by sw.wait_time desc


但是这还有一缺点就是


select 1 userid from dual


这个1没有在v$sqlarea.bind_data中


但where子句中的绑定变量还是有了