Blocking Session.


  Hi have a blocking session in the database that has been blocking 2 other sessions for about one week.

  I decided to kill in the session, but it is still there blocking the others 2.   I have tried many times  kill it  but the session it is still there.

  Any ideas on how to kill it for good ?  -

  What is not common is the fact that no user is complaning regarding this blocking.  
  We do not have performance issues or other problems.

joe_echavarriaDatabase AdministratorAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

slightwv (䄆 Netminder)Connect With a Mentor Commented:
How are you killing it?

The alter system kill session command will kill it.

Just because you kill it doesn't mean it goes away immediately.  Oracle will have to perform cleanup before it removes the session.  There may be a lot of ROLLBACK being performed.
joe_echavarriaDatabase AdministratorAuthor Commented:
I am killin it using  " alter system kill session ".

Is been two days and it is still there.
slightwv (䄆 Netminder) Commented:
I wonder if it is possible it was blocked somehow and the kill has to wait to be able to roll back?  Just a theory.  

I can't even think of a way to set up a quick test to see if it is possible to get a session into this state.
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Wasim Akram ShaikCommented:
if you want to kill that abruptly.. then you can terminate the session by using its process id at os level

select * from v$process where addr in(select paddr from v$session where sid=<sid_of_session>)

refer the column spid from the above result and kill the process

if you are in unix then use the command
kill -9 <spid_from_above>
slightwv (䄆 Netminder)Connect With a Mentor Commented:
>>kill -9 <spid_from_above>

Same as an 'alter session kill' SQL call.  If there is something keeping that from removing the process, 'kill -9' won't work either.  The process my disappear from the OS list but the database session will still exist until Oracle can clean it up.
Praveen Kumar ChandrashekatrDatabase Analysist Senior Commented:
Some times cleanup processing may take a considerable amount of time, in which case the session status is set to “marked for kill" until the process is complete.

In UNIX and Linux environments, the kill command is used to kill specific processes.

process ID associated with each running job in the spid column With this information, the operating system process can be killed by issuing the following command:

#Kill -9 process_id

You can also try immediate in alter system kill session as shown below


check these doc for reference
slightwv (䄆 Netminder) Commented:
>>In UNIX and Linux environments, the kill command is used to kill specific processes.

See my comment on this above:  http:#a40015574  The database still has to clean up.
Wasim Akram ShaikCommented:
=> The process my disappear from the OS list but the database session will still exist until Oracle can clean it up.

Steve, is this possible, I mean, the process would continue to be there with same sid even if the os process disappear? I wonder..!!!, even serial# remains the same??
slightwv (䄆 Netminder) Commented:
>>Steve, is this possible

Think about it:  Can Oracle leave in-doubt transactions around if a server process is killed?  Wouldn't be much of a database product if it did.

Cannot say if it is the same sid or serial#.  Not sure if/when Oracle changes them.
joe_echavarriaDatabase AdministratorAuthor Commented:
When i   " select * from v$session where state <>'WAITING';  "

The status of the session is " KILLED", but it still appears in the "blocking sessions" tab in the Entreprise Manager could control 12c and also in this query results.

So ,  just wait until it disappear ?

Meanwhile it is still blocking others two sessions.
slightwv (䄆 Netminder) Commented:
>>So ,  just wait until it disappear ?

Or figure out what, if anything, is blocking it from being killed.  Sorry, I don't know how to find this.

You might think about opening an SR with Oracle Support and tell them you have a killed session that has been killed for two days that is blocking other sessions.  They will have the tools to allow you to figure out why it hasn't died yet.
Wasim Akram ShaikCommented:
==> if anything, is blocking it from being killed

why don't you check the sql_id and retrieve the sql which its doing and perform a select using the same sql_id

select * from v$session where sql_id=<sql_id_from_killed_sid>

check out the event of the session which you have killed, what is actually doing over there from past 2 days or more.. may be you can figure out some thing from this.

Even after these steps if you are clue less then do log a SR with oracle as Steve suggested, they too will ask you these trouble shooting steps before actually diagnose your system
johnsoneSenior Oracle DBACommented:
In my experience, I have found that killing a process at the OS level with the kill -9 command can (not will) make the cleanup faster.  It may not make it faster, but it can.  From what I have seen is that if you kill the process at the OS level, the cleanup moves from the pmon process to the smon process.  If pmon is overly busy and smon isn't, then the cleanup can go faster.  However, in this case where the session has already been killed with an alter system, I'm not sure the cleanup will switch from pmon to smon.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.