Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Blocking Session.

Posted on 2014-04-22
13
Medium Priority
?
701 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 2
  • +2
13 Comments
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40015471
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
ID: 40015508
I am killin it using  " alter system kill session ".

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

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40015557
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 16

Expert Comment

by:Wasim Akram Shaik
ID: 40015565
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 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 total points
ID: 40015574
>>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:Praveen Kumar Chandrashekatr
ID: 40015584
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
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40015595
>>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
ID: 40015601
=> 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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40015638
>>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
ID: 40016083
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 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40016090
>>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
ID: 40016808
==> 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 35

Expert Comment

by:johnsone
ID: 40017433
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

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
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…
Via a live example, show how to take different types of Oracle backups using RMAN.

604 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