Pages

Tuesday, November 25, 2014

PL/SQL compilation of a package is stuck due to a DDL Lock. Investigation and Resolution

Issue
Compilation of a package is taking forever... 
The session then terminates with time out error.
Obviously the ALTER PACKAGE COMPILE is stuck due to a lock.

Details
Compilation command:
ALTER PACKAGE MY_PKG COMPILE BODY;

Check status of the package in question   
SELECT * FROM USER_OBJECTS WHERE status='INVALID';
Indeed the package is in invalid state.


Investigation:
Query DBA_DDL_LOCKS

SELECT owner, session_id, mode_held 
FROM DBA_DDL_LOCKS WHERE NAME='MY_PKG';

OWNER                   SESSION_ID MODE_HELD
----------------------- ---------- ---------------
ORA_USER                      1536 Null
ORA_USER                      1536 Null
ORA_USER                      1625 Exclusive

Get involved sessions details
SELECT p.spid, 
       s.osuser, 
       s.program,
       s.machine AS "SERVER",
       s.sid,
       s.serial#,
       s.username
  FROM v$process p, 
       v$session s 
 WHERE p.addr=s.paddr
   AND sid IN (1536,1625);

SPID  OSUSER    PROGRAM                       SERVER     SID SERIAL# USERNAME
----- --------- --------------------          --------- ---- ------- ------------------------------
15071 unix_user JDBC Thin Client              my_server 1536    3759 ORA_USER
30611 unix_user sqlplus@my_server (TNS V1-V3) my_server 1625   47548 ORA_USER

Session 30611 is the sqlplus session, requesting exclusive lock on the package, for compilation.
Session 15071 was opened by a Java client, holding a DDL lock on a package... Very weird.

Resolution
Kill the session holding the lock on Package.   
ALTER SYSTEM KILL SESSION '1625,47548' IMMEDIATE;

Then run the compilation again.
ALTER PACKAGE MY_PKG COMPILE BODY;

It should complete within few seconds.

DDL_LOCK_TIMEOUT
New from Oracle11.
It is possible to set timeout limit due to a DDL lock.
If the object is still locked after n seconds, we get an error, instead of the session being stuck, until there is a timeout error. 

Syntax:
ALTER SYSTEM/SESSION SET DDL_LOCK_TIMEOUT=15; (in seconds)

No comments:

Post a Comment