Oracle user disconnected without committing/rolling back, not longer can change database

Question

A user logged into the Oracle database I'm working with logged out without committing or rolling back and now my database cannot be changed, it seems to be locked.

I tried

 rollback

but that didn't work.

Any thoughts? Thanks.

Answer 1

It is impossible to log out of an Oracle database without committing or rolling back. It is possible that the client application crashed or was forcibly terminated and did not have a chance to log out leaving a session on the server that is holding some locks. If that is the case, the DBA would need to kill that session.

If you are the DBA and you're not sure what session needs to be killed, you can run the following query to get information about the various sessions that are holding locks that are blocking other sessions

SELECT ses.sid, ses.serial#, ses.username, ses.program, ses.osuser, ses.machine
  FROM v$session ses,
       dba_blockers blk
 WHERE blk.holding_session = ses.sid

Once you've identified which session(s) to kill

ALTER SYSTEM KILL SESSION '<<sid>>, <<serial#>>'

Answer 2

If the session did not rollback explicitly, if the database server can no longer reach a client its activity will be rolled back automatically, and then it will terminate the session.

The server is often quite happy if the client doesn't make a request to it for hours on end. See if you have SQLNET.EXPIRE_TIME set to anything on the server. If it is a non-zero value, then that's how many minutes it will wait before checking to see if a client connection is dead. If it is zero, it won't check and will only be aware if it finds the client is dead when responding to a client request.

Also, the rollback may take some time. If a lot of work as been done, it may take hours. Anything waiting on that session will continue waiting until that rollback is complete