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