The main issue in this post is a simple script, you can use to find sessions holding an object, and thus disabling the possibility to compile it. The script generate kill -9 commands to clear their shadow processes at the OS level.
select 'kill -9 '||b.spid||' # username '||c.username||''
from v$access a, v$process b, v$session c
where a.object=upper('&name')
and a.sid=c.sid
and b.addr=c.paddr
/
If you want to have an example of using this script you can continue reading :-)
This morning we find that a package holding a new version was invalid, because a dependent object was not changed accordingly, as projected to be.
The dependent package, that remain unchanged, was being executed by about ~200 from ~5000 concurrent users.
The options were:
- rollback the package that failed compilation or
- introduce the new version of the dependent, and after that recompile the invalid object.
We decided to introduce the changes to the dependent.
In one screen I did run this script to generate kill -9 commands for all shadow sessions holding the dependent,
set echo off head off veri off feed off pages 50000
/
spool killsess.sh
/
select 'kill -9 '||b.spid||' # username '||c.username||''
from v$access a, v$process b, v$session c
where a.object=upper('&name')
and a.sid=c.sid
and b.addr=c.paddr
/
spool off
On other screen I did start an sqlplus session to compile the new version, wrote down the compile command and left the screen open, ready to hit the enter key.
Once the generate of kill -9 commands finished I did set execution privileges for the script and executed it, from within sqlplus
!chmod 700 killsess.sh
!./killsess.sh
After that, immediately I did compile the dependent, and the invalid object from the second screen:
@compile_version;
alter package <pkgname> compile body;
