Introduction
Oracle by default does not automatically commit a transaction (Autocommit is typically off). Transaction Management (Commit or Rollback) is left to the client as this askTom post explains nicely. The tool you use may provide this option for you. For e.g., SQL*Plus commits any open transaction upon exit. So, even if you don’t have COMMIT at the end of a script, when SQL*Plus will issue a COMMIT when the SQL is exited.
This was the default behavior for a long time and was generally accepted. Some developers wanted to have more control over it, and according to this stackoverflow post, there was even a bug (633247) opened for it in 1998! Recently, in Oracle 11g, this has finally been changed. Now, as of Oracle 11g R2, the user actually has an option (EXITCOMMIT) to tell SQL*Plus whether to COMMIT or ROLLBACK upon EXIT. The following statement makes SQL*Plus roll back a transaction upon exit.
set exitcommit off
See this Oracle Post for a good example.
Notes
- One poster in the Stackoverflow post above, actually mentions about
Autocommit option in SQL*Plus. I just want to clarify:
SQL*Plus does have an option to set Autocommit on or off. This is actually meant for every statement that you issue, not just the last statement before exit. Prior to Oracle 11gR2, this did not have an impact on the Commit on Exit!
- Also, this change is actually in client tool (SQL*Plus) not in the database. So, if you are using an older client (like I am), you will be disappointed not to find this option!
References
Filed under: CodeProject, Databases, Oracle Tagged: Autocommit, COMMIT, Oracle, Oracle 11g, SQL*Plus 
Originally a Physics major, fell in love with Microprocessors and switched to Computer Science 20+ years ago. Since then, dabbled in various languages including, PowerBuilder, Oracle, Java, C, C++, Perl, Python etc. Constantly striving for quality and performance too.
I try to help fellow developers with technology as a way of "giving back to the community". Blogging became a natural extension of that effort. Still learning to perfect that art. If one new programmer out there benefits from this blog, my time and effort are fully worth it.
The underlying theme in my blogs is power and beauty of programming (and technology in general). A well written program gives me the sense of awe you get when you look at a man made wonder like Angkor Wat. You experience poetry, art, mystique, power all at once. A program and the troubleshooting that ensues also gives you a feeling you get while reading a mystery novel!
Submit an article or tip
