Menu
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.
628
Use an Autonomous Transaction So even though the second create table does commit, this leaves the original transaction alone. So you can still roll it back. If you need to create a table part-way through a transaction before 18c, this is the way to do it.
Introduction. Do you think it's impossible to rollback your changes after you commit a Delete command? Well, it's possible. In version 11g, Oracle keeps snapshots of your table for some time and allows you to rollback to a particular snapshot as long as this period of time is not passed.
Use the ROLLBACK statement to undo work done in the current transaction or to manually undo the work done by an in-doubt distributed transaction. Note: Oracle recommends that you explicitly end transactions in application programs using either a COMMIT or ROLLBACK statement.
Oracle Database issues an implicit COMMIT before and after any data definition language (DDL) statement. Oracle recommends that you explicitly end every transaction in your application programs with a COMMIT or ROLLBACK statement, including the last transaction, before disconnecting from Oracle Database.
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#>>'
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
22
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
