Oracle Labs by Yuri Khazin, Oracle DBA

May 2, 2013

New in Oracle 11g. Rollback on exit in sqlplus

Filed under: Oracle Database — Tags: , , , — oraclelabs @ 10:25

For a very long time it’s been a standard feature of sqlplus to commit on exit. This feature could have been overwritten only by a “WHENEVER SQLERROR EXIT ROLLBACK”, but there was no control for rollbacks when we exit normally. Having autocommit disabled (I am grateful it is disabled by default) and now disabling commit on exit will be very useful in a situation when your sqlplus session is really interactive and you would not appreciate a commit if you exit accidentally.

Here comes in a handy new feature in 11g:

SET EXITCOMMIT OFF (Default is ON)

Refer to the documentation on this feature (there is also info on AUTOCOMMIT and how that affects EXIT behavior).

If you want to set this and other features in your sqlplus automatically, you can use what Oracle calls “Default Site Profile Script“:

$ORACLE_HOME/sqlplus/admin/glogin.sql

%ORACLE_HOME%\sqlplus\admin\glogin.sql

A user profile (login.sql) can also be used for this purpose, provided that it is located some place where SQLPATH variable points (this is also described in the doc links above).

 

For some reason, most of the Google search links on this subject led me to the “whenever” command, rather than to “set”.

Create a free website or blog at WordPress.com.