Background: The database has a long-term execution of stored procedures resulting in excessive resource consumption, the following is expected to quickly locate stored procedures, rapid intervention processing, restore database performance. Long-term operation through the following statements? T more than the database, a.
--Query the stored procedure being executed
SELECT * from V$db_object_cache where locks > 0 and pins > 0 and type= ' PROCEDURE ';
--Query activity execution stored procedure session ID
Select b.sid,b.serial#
From SYS. V$ACCESS A, SYS. V$session b
where A.type = ' PROCEDURE '
and (A.object like Upper ('%pr_sea_pad_jkf_lgk_goods_top% ') or
A.object like Lower ('%pr_sea_rtk_jkf_e_entry_count% '))
and A.sid = B.sid
and b.status = ' ACTIVE ';
--Stop session
Alter system kill session ' 539,2022 ';
Oracle Series Script 2: Lifesaving stored procedure emergency processing scripts