Oracle 查看正在执行的存储过程（转载）

正在执行的存储过程

select owner,name from v$db_object_cache where type like '%PROCE%' and locks >0 and pins >0;

查找正在执行的存储过程的sid

   select   b.sid,b.SERIAL#,a.OBJECT, 'alter system kill session   ' || '''' || b.sid || ',' ||b.SERIAL# ||  ''';' kill_command
               from   SYS.V_$ACCESS a, SYS.V_$session b
               where    a.type = 'PROCEDURE'
                  and   (a.OBJECT like upper('%存储过程名%') or
                            a.OBJECT like lower('%存储过程名%'))
                 and a.sid = b.sid
                 and b.status = 'ACTIVE';

杀掉正在执行的procedure

select b.sid,b.SERIAL#,a.OBJECT, 'alter system kill session ' || '''' || b.sid || ',' ||b.SERIAL# || ''';' kill_command from SYS.V_$ACCESS a, SYS.V_$session b

where a.type = 'PROCEDURE' and (a.OBJECT like upper('%存储过程名%') or a.OBJECT like lower('%存储过程名%'))

and a.sid = b.sid and b.status = 'ACTIVE'; 2、Kill 正在执行的存储过程 alter system kill session 'sid,SERIAL#';

查找正在执行的sql

select a.program, b.spid, c.sql_text,c.SQL_ID
from v$session a, v$process b, v$sqlarea c
where a.paddr = b.addr
and a.sql_hash_value = c.hash_value
and a.username is not null

 

整理自：

https://blog.csdn.net/qq_35187203/article/details/104172667

https://www.cnblogs.com/JIKes/p/11688437.html