Why does SQL*Plus commit on exit?

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

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 SQLPlus, the session's transaction should be rolled back. But if the SQLPlus 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.)

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?