ORACLE series script 2: life-saving stored procedure emergency handling script

Background: the long-term execution of stored procedures in the database leads to excessive resource consumption. Through the following expectations, we can quickly locate stored procedures, quickly intervene processing, and restore database performance. Long-term operation and maintenance through the following statements? t or above database? Yes, it works every time.

-- query the executing stored procedure

Select * from v$db_object_cache where locks > 0 and pins > 0 and type='PROCEDURE'

-- query the execution stored procedure session id of the activity

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 the session

Alter system kill session '539 and 2022'