Home
Main Menu
Home
Help Docs
Newbies
Corporates
Gnu/Linux Distros
Programming
Databases
Links
Search
Regular
News
Interviews
Aaj Ka Tip
Aaj Ka Command
Community
Blogs
Wiki
Forums
Services
Free Software
Latest
Popular
Who's Online
We have 39 guests online

Killing sessions in Oracle Print E-mail
    

Killing inactive sessions in Oracle

The below mentioned method will help you to identify the inactive session in Oracle database and then kill them thereby freeing the resources.

This should not be done in production environment since there are other ways of handling load in production environment.

like using a shared server instead of dedicated oracle server.

                           
        1.    Change parameter in spfile/pfile and restart DB

            resource_limit = true

          2    Make new profile with idle_time

CREATE PROFILE IDLEPROFILE LIMIT IDLE_TIME 60;

      Change the idle time (60 mins. in this case ) to any other time.

           3.    Add users to that profile

ALTER USER PROFILE IDLEPROFILE;

    Add the profile to other users the same way as done above for user "SCOTT"

           4.    Place the scripts for Oracle user and root user

Script to be run by the Oracle User

#!/bin/sh

export ORACLE_BASE=/u01/app/oracle

export ORACLE_SID=oracle

export ORACLE_OWNER=oracle

ORACLE_HOME=/u01/app/oracle/oracle/product/10.2.0/db_1

PATH=$PATH:$HOME/bin:$ORACLE_HOME/bin:

export PATH

tmpdir=/tmp

NEW=`date +%Y_%m_%d`

touch $tmpdir/$NEW

#echo $workdir/$NEW

tmpfile="$tmpdir/$NEW"

#tmpfile=/tmp/$tmp

sqlplus system/password <<EOF

spool $tmpfile

select p.spid from v\$process p,v\$session s

where s.paddr=p.addr

and s.status='SNIPED';

spool off

EOF

Script to be run by the “root” user.

#!/bin/sh

tmpdir=/tmp

NEW=`date +%Y_%m_%d`

tmpfile="$tmpdir/$NEW.lst"

for x in `cat $tmpfile | grep "^[0123456789]"`

do

kill -9 $x

done

rm $tmpfile

It is best to automate the execution of the Oracle user script a minute before the root user. Crond can be used in Unix/Linux for the same.


< Previous   Next >



Polls
Have you visited this site earlier?
  
gnulinuxclub
Join Us
About Us
Contact Us
Support Us
Acknowlegdement
Login(only for dev.)

Royalty Free Images

O'Reilly User Group discount!




Powered By GIMP GIMP
Contact Webmaster Copyleft 2011 gnulinuxclub.org