Oracle Account Not Responding

Question

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.

Answer 1

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...