--在数据库管理员用户下
-- select * from gv$access
-- select * from gv$session
select b.sid,b.serial#,b.osuser,b.username,b.machine,b.terminal,b.program,a.owner,a.object,b.status,b.sql_exec_start,
       'alter system kill session '''||b.sid ||','||b.serial#||''';' as kill_command
from gv$access a,gv$session b
where a.sid=b.sid
and b.status='ACTIVE'
and a.type in('PROCEDURE','PACKAGE')
and a.owner not in('SYS','MDSYS','SYSTEM','XDB')
and a.object like upper('pr_test%');

2、杀掉会话
alter system kill session 'sid,serial#';


3、查看执行SQL
select sid,user_name,sql_text
-- select *
from v$open_cursor
where lower(sql_text) like '%pr_test%'

4、另一种查询方法
select owner,name,status,timestamp
-- select *
from gv$db_object_cache
where locks > 0
and pins > 0
and type='PROCEDURE';

5、另一种查询方法
-- select * from gv$session
-- select * from dba_ddl_locks
select a.session_id sid,b.SERIAL#,b.username,a.owner,a.name,a.type,a.mode_held held,a.mode_requested request,
       b.osuser,b.machine,b.terminal,b.program,b.logon_time,b.sql_exec_start
-- select *
from dba_ddl_locks a,gv$session b
where a.session_id=b.sid
and a.owner=b.username
and a.owner not in('SYS','MDSYS','SYSTEM','XDB')
and a.type like '%Procedure%'
and a.name=upper('pr_test');