Solved

Blocking Session.

Posted on 2014-04-22
13
549 Views
Last Modified: 2014-05-19
Hi,

  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.

Regards,
0
Comment
Question by:joe_echavarria
  • 6
  • 3
  • 2
  • +2
13 Comments
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
How are you killing it?

The alter system kill session command will kill it.

Note:
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.
0
 
LVL 1

Author Comment

by:joe_echavarria
Comment Utility
I am killin it using  " alter system kill session ".

Is been two days and it is still there.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
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.
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
Comment Utility
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>
0
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
>>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.
0
 
LVL 12

Expert Comment

by:praveencpk
Comment Utility
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

ALTER SYSTEM DISCONNECT SESSION 'sid,serial#' IMMEDIATE;

check these doc for reference
http://www.dba-oracle.com/t_alter_system_kill_session.htm

http://www.oracle-base.com/articles/misc/killing-oracle-sessions.php
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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.
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
Comment Utility
=> 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??
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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.
0
 
LVL 1

Author Comment

by:joe_echavarria
Comment Utility
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.
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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.
0
 
LVL 16

Expert Comment

by:Wasim Akram Shaik
Comment Utility
==> 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>

else
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
0
 
LVL 34

Expert Comment

by:johnsone
Comment Utility
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.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now