PLSQL Locked package

PL SQL locked package

When you work in a large company that has many programmers, you can often find yourself in situation of locked package. Locked package? Yes, that is right. Locked package is the thing that happens when somebody else compile that package or is debugging it and you are also working on that package, but with no team coding option. If person A is debugging package, and person B let's say wants to compile package he will end up in a "wait session" until person A stops debugging. Personally I had situation that Person A debugged package and left package in debug mode. So i waited and waited but I couldn't test program so this code came in handy.... So in you're Sql developer (Oracle developer) enter this code

SELECT
   vss.SID,
   vss.SERIAL#,
   vss.osuser,
   vss.machine,
   vs.sql_text,
   dd.*
FROM
   v$session vss,
   DBA_DDL_LOCKS dd,
   v$sql vs
WHERE
   name LIKE 'NAME_OF_YOUR_PACKAGE' and  --ime paketa
   vss.sid=dd.session_id   and
   vs.sql_id = vss.sql_id  and
   rownum < 2;

Let's say  NAME_OF_YOUR_PACKAGE is TEST_TURKI so if you run it on Oracle 11g database you will see if someone is using it...

by data that is showing we can see that user under SID 4527 and SERIAL 145 is using it.
We now can perform kill session command to get back access to that specific package.

So if you have database administrator privileges you can type 

execute kill_session(SID,SERIAL#);

in this particular case command would be;

execute kill_session(4527,145);

and you will get message that is killed or it is rolling back.

so that's it you will gain access to your package in a few seconds or minutes (depending how large is package or what that user is doing)

One more trick to kill session is that in ORACLE SQL developer you can choose Tools.>Monitor sessions
and find user that is holding you're package

When it's opened you can find it under SID and SERIAL and right klick him with Kill session option

That's it...