Surely this should be the same as a termination of a session and cause a rollback? It seems to me to be the most un-Oracle thing possible. I was actually shocked when I found out that it did this
More importantly - would anyone object if Oracle changed it to rollback on exit?
You'd have to ask Oracle!
I must admit that I was surprised when I first discovered this, since you'd think it would take the more conservative approach which would be to do a ROLLBACK.
I can only guess that COMMIT is considered to be the most likely / default action and maybe this is why SQL*Plus does it?
It was a design decision by Oracle, probably made more than 20 years ago. It is not the design I would have used. Note that it seems to be a property of SQL*Plus, not of the underlying OCI.
If the session terminates abruptly, AFAIK, the session is rolled back, as you'd expect. So, for example, if someone sends a SIGKILL to SQL*Plus, the session's transaction should be rolled back. But if the SQL*Plus session terminates gracefully (EOF or exit command), then SQL*Plus in its infinite wisdom decides to commit whatever you've done so far.
As to why - I have a theory. In SQL standard databases, you are always in a transaction, even if the only operation you've performed is a SELECT statement. If you don't commit, then any changes you make are rolled back. It is easy to forget to add commit to the end of scripted operations, so making it the default behaviour reduced the number of times someone ran a script to change the database and then ran a second script to see whether the changes took effect correctly. Other DBMS obviate the need for this with modes like 'auto-commit', where each statement is a standalone transaction, automatically committed on completion. That's a useful mode of operation. Other systems provide a mode where you are in auto-commit until you run an explicit BEGIN WORK statement, whereupon (of course), you are in a transaction until the corresponding COMMIT or ROLLBACK. I have been caught out by 'MODE ANSI' databases not committing sufficiently often to make sure that I commit when it matters, but the software I use (not Oracle) still rolls back uncommitted work rather than silently committing it for you - and I would be unhappy if it was changed to work otherwise. (I suppose a configurable default might be OK; I still think rollback uncommitted is the better default, for all it is a nuisance to the unaware; there is less danger of accidentally corrupting a database, and that is of paramount importance to me.)
(Due notice: This is second-hand information from someone who works for another DBMS vendor. It is, however, accurate as far as I know, and based on information accumulated over a period of more than a decade and after asking related questions in various forums.)
I think the commit is a good idea and I agree with what Justin and Billy wrote in this thread: http://forums.oracle.com/forums/thread.jspa?messageID=3611345&#3611345
Regards, Rob.
Good question.
I had a look on metalink and a bug (or change request) has been raised against the default behaviour of committing on normal exit back in 1998. If you have access to metalink look for bug 633247.
Funnily enough, with the 11gR2 release this week (2009-09-03), SQL*Plus now has an option to COMMIT or ROLLBACK on EXIT. Doc here
I'd guess in the next few weeks/months, there'll be an 11gR2 Instant Client which you can use against your current database and get your desired behaviour
A caution to be aware of. If you DISCONNECT or CONNECT to a different session, it will still implicitly commit the transaction (according to the doc).
Not exactly answering the question as to the why of this behaviour, but you
can change the default behavior with set autocommit off.