在linux 系统中 用top命令查出CPU最高的SPID，再将SPID给存储过程，可以查出该进程的SQLTEXT
create or replace procedure pro_get_sqltext(pin_spid in varchar2,
pout_sqltext out varchar2,
pout_sqltests out clob,
pout_sql_kill_sid out varchar2) is v_spid number := 0;
v_sid number := 0;
v_serial number := 0;
v_sqltext varchar2(4000) := '';
v_sqltexts clob:='';
v_sql_kill_sid varchar2(4000) :=''; begin begin
select SID, serial#
into v_sid, v_serial
from v$session
where paddr in (select addr from v$process where spid in (pin_spid)); v_sql_kill_sid:=' alter system kill session '||''''||v_sid||','||v_serial||''''||';';
pout_sql_kill_sid:=v_sql_kill_sid; exception
when NO_DATA_FOUND THEN
pout_sqltext := 'the sid do not be founded';
v_sql_kill_sid := 'the sid do not be founded';
end; begin
select q.SQL_TEXT,q.SQL_FULLTEXT
into v_sqltext,v_sqltexts
from v$sqlarea q
where exists (select *
from v$sqltext a
where exists (select sql_hash_value
from v$session b
where b.SID = v_sid
and a.HASH_VALUE = b.sql_hash_value)
and q.SQL_ID = a.SQL_ID); pout_sqltext := v_sqltext;
pout_sqltests:=v_sqltexts; exception
when NO_DATA_FOUND then
pout_sqltext := 'the SQL_TEXT do not be founded';
end;
commit; end pro_get_sqltext;
存储过程:通过操作系统进程查询SQL
SELECT s.SID,
p.SPID,
q.SQL_TEXT,
q.SQL_FULLTEXT,
s.LOGON_TIME,
s.STATUS,
q.CPU_TIME/1000/1000 "minutes",
q.ELAPSED_TIME/1000/1000 "minutes"
FROM v$process p, v$session s, v$sql q
where s.PADDR = p.ADDR
and q.SQL_ID = s.SQL_ID
and s.TYPE = 'USER'
and s.STATUS='ACTIVE'
;
查询SQL
分解SQL如下：
select s.SID,
s.serial#,
s.username,
s.osuser,
s.machine,
s.program,
s.process,
to_char(s.logon_time, 'yyyy/mm/dd hh24:mi:ss') logon,
p.spid
from v$session s,v$process p
where 1=1
and s.PADDR=p.ADDR
and P.spid in ('')
; select sql_text,a.SQL_ID
from v$sqltext a
where a.HASH_VALUE=(select sql_hash_value
from v$session b
where b.SID='')
order by piece ASC; select * from v$sqlarea q
where q.SQL_ID='akf0uyy10kgn9'
; --------------------- select *
from (select q.SQL_ID,q.SQL_TEXT, q.SQL_FULLTEXT,s.SID,s.SERIAL#
from v$sqlarea q,v$session s
where q.SQL_ID=s.SQL_ID
and LAST_ACTIVE_TIME>=to_date('2016-05-03 08:00:00','YYYY-MM-DD HH24:MI:SS')
AND INSTR(PARSING_SCHEMA_NAME,'SYS') <=0
order by cpu_time desc)
where rownum <= 15
order by rownum asc; alter system kill session '634,40971';
SQL通过SPID查询SQLTEXT