X

Alejandro Vargas' Blog

  • October 30, 2007

How to Generate a Script to Kill the Sessions Holding an Object

Alejandro Vargas
Technical Leader, ACS Global Delivery, Infrastructure & BigData

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;



      





Join the discussion

Comments ( 1 )
  • mehmet eser Wednesday, November 21, 2007
    Will it be an option to kill the sessions from the database ?
    is there a problem with Killing them from OS ?
    any side effects ?
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.Captcha
Oracle

Integrated Cloud Applications & Platform Services