Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

I have an Oracle instance which has user1, user2 and user3 accounts. Yesterday I was able to log in to all three accounts. Today, I can get in to user1 and user3, but user2 is completely 'frozen' in some way that I don't understand.

If I try to log in to user2 with sqlplus it just spins forever. It does not connect, does not time out, nothing happens until I press CTRL+C to kill the process. Connecting as user1 or user3 is instantaneous.

I thought I would try locking user2 out and then try unlocking it. The query to lock the user ran for 25 minutes before I gave up! Locking user1 then unlocking user1 ran instantly.

Using TOAD and connecting as a DBA, I used Session Browser to investigate. I found 11 connections to the database as user2. Five of these appear to be my failed attempts at connecting using sqlplus. None of these connections are showing any open cursors, a current statement or any locks. On the waits tab 10 of the connections show a "row cache lock" with:

  • seconds in wait between 3,000 and 60,000
  • P1 = 7
  • P1 Text = "cache id"
  • P2 = 0
  • P2 Text = "mode"
  • P3 = 3
  • P3 Text = "request"

One of the connections stands out as it appears to be very old. It shows a "SQL*Net message from client" with:

  • seconds in wait > 600,000
  • P1 = 1413697536
  • P1 Text = "driver id"
  • P2 = 1
  • P2 Text = "#bytes"
  • P3 = 0
  • P3 Text = ""

I am not able to kill any of these 11 sessions. After I issue the kill command (using TOAD, with or without immediate option) it runs for 45-60 seconds then says "Session is marked for kill." but the session never goes away.

Any ideas what this means or how I can kill these sessions and restore access to the user2 account?

Update: There were some interesting lines in the alert log:

Tue Dec 29 09:37:45 2009
WARNING: inbound connection timed out (ORA-3136)
Tue Dec 29 10:25:45 2009
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=17
System State dumped to trace file [snip]\udump\ora_1988.trc
Tue Dec 29 10:54:17 2009
WARNING: inbound connection timed out (ORA-3136)
Tue Dec 29 10:55:47 2009
WARNING: inbound connection timed out (ORA-3136)
Tue Dec 29 10:56:47 2009
WARNING: inbound connection timed out (ORA-3136)
Tue Dec 29 10:57:47 2009
WARNING: inbound connection timed out (ORA-3136)
Tue Dec 29 11:12:17 2009
WARNING: inbound connection timed out (ORA-3136)
Tue Dec 29 12:06:17 2009
WARNING: inbound connection timed out (ORA-3136)
Tue Dec 29 12:26:47 2009
WARNING: inbound connection timed out (ORA-3136)
Tue Dec 29 12:27:47 2009
WARNING: inbound connection timed out (ORA-3136)
Tue Dec 29 13:46:17 2009
WARNING: inbound connection timed out (ORA-3136)
Wed Dec 30 10:02:16 2009
>>> WAITED TOO LONG FOR A ROW CACHE ENQUEUE LOCK! pid=37
System State dumped to trace file [snip]\udump\ora_2860.trc
Wed Dec 30 11:55:59 2009
orakill: attempting to kill tid=436
Wed Dec 30 11:56:04 2009
orakill: ssthreadkill(tid=436) unable to get the thread list mutex: err=0

Resolution: It seems like this is a 10.2.0.3 bug and I need to restart the instance, which I do not have permission to do, so I will have wait a few days.

share|improve this question
    
What version of the database is this? The orakill message at the end is seen in 10.2.0.3 and is a bug. –  DCookie Dec 30 '09 at 22:12
    
Look in udump\ora_2860.trc for messages pertaining to the enqueue lock. –  DCookie Dec 30 '09 at 22:35

1 Answer 1

up vote 2 down vote accepted

Try the following:

select s.username, s.sid, p.spid 
  from v$session s join v$process p on addr=paddr
where s.username = 'USER2';

Using the spid value from the above query, log on to the server and issue the following command from a DOS command prompt:

orakill YOURSID spid

YOURSID is the SID of the database instance you're dealing with.

orakill often works when the GUI tools fail to kill processes. Here is a nice overview of orakill. Note reason #1 for using orakill - it might explain why you can't use your GUI tool:

The alter system statement will not clear any locks that exist. Instead, the session will remain connected until it times out, then the session is killed and the locks are released. The orakill command will kill the thread and the locks instantly.

Update:

You can also try:

select sid, serial#, program from v$session;
alter system kill session '<SID>,<SERIAL#>' immediate;

although I wouldn't hold out a lot of hope...

share|improve this answer
    
I ran orakill and it returned "Kill of thread id 436 in instance [MYSID] successfully signalled." but I'm still seeing all 11 rows being returned by the query against v$session and v$process. –  wweicker Dec 30 '09 at 20:05
    
I assume you tried to kill the oldest session. Is there anything of interest in the alert log? From an SQL*Plus prompt, run "show parameter background_dump_dest", which will tell you where on the server the directory where the alert log is kept. –  DCookie Dec 30 '09 at 21:46
    
Thanks for the instructions! I updated the question with a snippet from the bottom of the alert log. I don't see anything interesting over the past two months until what I posted started this week. There were more 'orakill: attempting to kill tid=' lines after the end of this snippet that I left out for brevity. –  wweicker Dec 30 '09 at 22:10
    
If this is 10.2.0.x, x < 4, you may have to restart the instance to free this user account up. –  DCookie Dec 30 '09 at 22:17
    
I checked v$version and indeed this is 10.2.0.3.0. –  wweicker Dec 30 '09 at 23:00

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.