If your application has a lot of deeply linked pl/sql package and you recompile all the packages sometimes the recompile process hangs.
The compiling session has long wait times on the wait event “library cache pin”.
One reason may be that a background job using the same pl/sql packages or a pl/sql packages that depend on the recompiled packages.
With these statements you can find the blocking session:
SELECT s.sid ,s.serial# ,SQL.SQL_TEXT ,holder.event AS holder_wait_event ,holder.p1raw AS wait_event_param_one ,holder.p2raw AS wait_event_param_two ,holder.p3raw AS wait_event_param_three -- How waits ,COUNT (s.sid) AS users_count_blocked ,waiter.p1raw AS waiter_param_one FROM v$sql SQL ,v$session s ,x$kglpn p ,v$session_wait waiter ,v$session_wait holder WHERE s.sql_hash_value = SQL.hash_value AND p.kglpnhdl = waiter.p1raw AND s.saddr = p.kglpnuse AND waiter.event LIKE 'library cache pin' AND holder.sid = s.sid GROUP BY s.sid ,s.serial# ,waiter.p1raw ,holder.event ,holder.p1raw ,holder.p2raw ,holder.p3raw ,SQL.SQL_TEXT;
So simulate the behavior:
Create procedure in Session A:
CREATE OR REPLACE PROCEDURE gpi$b AS BEGIN DBMS_OUTPUT.put_line ('Procedure B'); EXECUTE immediate 'alter procedure gpi$a compile'; END; / CREATE OR REPLACE PROCEDURE gpi$a AS BEGIN DBMS_OUTPUT.put_line ('Procedure A'); -- Call gpi$b gpi$b; END; /
In Session B:
BEGIN gpi$a; END; /
Now you can query with the statement above to find the self-blocking session B and kill this session completely.
SQL> ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;