betweenGo

I was always doing a commit before exiting SQL*Plus when it occurred to me today that maybe I didn’t need to do that.  Doing a Google search quickly answered that for me.

If you issue a graceful exit (via the “exit” or “quit” command), sqlplus will always issue a commit. However, if you were to be ungracefully disconnected, for example by closing your terminal window, then PMON will issue a rollback like it does with any other disconnected session.

sqlplus commit-on-exit?

Therefore there is no need to do a commit before you exit.

You can also set autocommit on, it is off by default, but I would not recommend doing this.

During interactive usage with sqlplus, Oracle also supports an AUTOCOMMIT option. With this option set to ON each individual SQL statement is treated as a transaction an will be automatically commited right after it is executed. A user can change the AUTOCOMMIT option by typing

 SET AUTOCOMMIT ON

or

 SET AUTOCOMMIT OFF

whereas by typing

 SHOW ALL

a user can see the current setting for the option (including other ones).

Oracle SQL Transactions

Related posts:

1. SQL Insert in One Table Based on Values in Another Table
2. ORA-12514 and ORA-27101
3. How to Retrieve Oracle Version Information
4. SQL Delete in One Table Based on Values in Another Table
5. SQL Update in One Table Based on Values in Another Table