oracle自定義過程來獲得完整的sql語句

eric0435發表於2012-12-31
OracleSQL
因為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子句中的繫結變數還是有了

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/26015009/viewspace-751947/，如需轉載，請註明出處，否則將追究法律責任。

相關文章