因为oracle将sql共享之后，截取出来的sql语句是带变量的，创建一个函数 jy_getsql来获得执行时的完整sql语句create or replace f
因为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子句中的绑定变量还是有了
，