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

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: