How to detect locked Oracle stored procedures and processing totals

1. See which stored procedure is locked
Check V$db_object_cache View
SELECT * from V$db_object_cache where owner= ' the owning user ' and locks!= ' 0 '

2. See which SID, through the SID, can tell which session
Check v$access View
SELECT * from v$access where owner= ' user ' and name= ' process name ' just found '
3. Identify SIDs and serial#
Check v$session View
Select Sid,serial#,paddr from v$session where sid= ' sid just found ';
Check v$process View
Select the SPID from v$process where addr= ' just found paddr ';
4. Killing process
4.1 First Kill Oracle Process
Alter system kill session ' detected SID, detected serial# ';
4.2 Again kill the operating system process
If it is a Unix
Kill-9 just identified the SPID.
If it is windows
Orakill just identified the SPID that SID just identified.

Http://www.cnblogs.com/zwl715/p/3699124.html

Reason: During the execution of stored procedures, shutdown or interrupt stored procedures, recompile is a long wait time, cannot be deleted

Solve:

Detect the SPID of the locked stored procedure first

SELECT spid

From V$db_object_cache OC,

V$object_dependency OD,

Dba_kgllock W,

V$session S,

V$process P

WHERE OD. To_owner = OC. OWNER

and OD. To_name = Oc.name

and OD. To_address = W.KGLLKHDL

and W.kgllkuse = S.saddr

and p.addr = S.paddr

and oc.name = ' sp_ps_datawwtpflowsum_u '//stored procedure name

Log on to the installation server where the locked stored procedure belongs to Oracle, start-> run->cmd, enter format Orakill SID instance spid such as: C:\>orakill ORCL 540

Http://www.cnblogs.com/zwl715/p/3699120.html