OraFAQ Forum: Server Administration » killing sessions in oracle 9i

Home » RDBMS Server » Server Administration » killing sessions in oracle 9i

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

killing sessions in oracle 9i [message #62642]

Wed, 04 August 2004 23:05

puneet
Messages: 76
Registered: August 2002

Member

friends ,

how can I kill more than one session simultaneously in oracle 9i.

though I can kill inactive sessions one by one using alter system command. but since I have more than 700 inactive sessions�so I want to kill all of them in one go.

pls advise.

rgds

PB

Report message to a moderator

Re: killing sessions in oracle 9i [message #62648 is a reply to message #62642]

Thu, 05 August 2004 16:09

andrew again
Messages: 2577
Registered: March 2000

Senior Member

select 'alter system kill session ''' || sid || ',' || serial# || '''; --'|| module 
from v$session where module like 'T%';

alter system kill session '15,71'; --T.O.A.D.
alter system kill session '18,577'; --T.O.A.D.
alter system kill session '24,255'; --T.O.A.D.
alter system kill session '31,111'; --T.O.A.D.

You can do a similar thing in PL/SQL.

Report message to a moderator

Re: killing sessions in oracle 9i [message #62651 is a reply to message #62648]

Thu, 05 August 2004 21:47

puneet
Messages: 76
Registered: August 2002

Member

thanks, it worked !!

I managed to do it but not sure whether my approach was correct. Pls advise.
I executed the select statement which gave me more than 800 'alter system kill session ...' statements as result. Then I copied those statements and executed again in SQL plus, which killed these sessions. But I had to execute these statements in a batch of 100. Is there any way I can run all the statements in one batch.

thanx
PB

Report message to a moderator

Re: killing sessions in oracle 9i [message #62658 is a reply to message #62651]

Fri, 06 August 2004 06:56

andrew again
Messages: 2577
Registered: March 2000

Senior Member

What happened after 100 sessions were killed? What error did you get? You need to be very careful whn killing session - you could kill your own or some background session.

Report message to a moderator

Re: killing sessions in oracle 9i [message #62692 is a reply to message #62658]

Mon, 09 August 2004 20:37

puneet
Messages: 76
Registered: August 2002

Member

I am killing only the sessions created by a particular user.
I don't get an error message. problem is if I copy all 800 kill session statements and paste them in SQL plus, the contents actually get truncated in between and if I copy & paste approx 100 lines, it works fine.

Report message to a moderator

Re: killing sessions in oracle 9i [message #62747 is a reply to message #62692]

Wed, 11 August 2004 07:23

rk_kk
Messages: 2
Registered: August 2004

Junior Member

Save the statements in a file. e.g c:oracle_scriptskill_sess.sql

at the SQL prompt

sql>@c:oracle_scriptskill_sess.sql

rk_kk

Report message to a moderator

Re: killing sessions in oracle 9i [message #62935 is a reply to message #62642]

Wed, 25 August 2004 01:47

selvam R
Messages: 1
Registered: August 2004

Junior Member

How to ensure that all the inactive sessions of oracle
(RAC AIX 4.3.3) is actually cleared. these inactive sessions are accumulated and resulted to the exceeding max processes (set as 500).

kinldy suggest this

thanks in advance

selvam

Report message to a moderator

Re: killing sessions in oracle 9i [message #62937 is a reply to message #62935]

Wed, 25 August 2004 02:03

nabila
Messages: 13
Registered: April 2003

Junior Member

with use of profile and idle_time

Report message to a moderator

Previous Topic:	Help me : Urgent
Next Topic:	Performance tuning of Oracle latches

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Jan 09 14:15:47 CST 2025