Rollback on exit in sqlplus

It’s been a standard feature of sqlplus to commit on exit. Sometimes you might want to set commit on exit off when your sqlplus session is really interactive and your would not appreciate a commit if you exit by mistake.

In Oracle 11g you can set exit commit off from your session or you can set it for every session from the sqlplus.

How to set it off from particular session:

SQL> SET EXITCOMMIT OFF

You can check the status of exitcommit by using the below commad

SQL> show EXITCOMMIT

exitcommit OFF

How to set it default for SQLPLUS:

You can set it or other features using Default Site Profile Script. The Site Profile script is $ORACLE_HOME/sqlplus/admin/glogin.sql in UNIX, and ORACLE_HOME\sqlplus\admin\glogin.sql in Windows. If a Site Profile already exists at this location, it is overwritten when you install SQL*Plus. If SQL*Plus is removed, the Site Profile script is deleted.

Example:

vi $ORACLE_HOME/sqlplus/admin/glogin.sql

--
-- Copyright (c) 1988, 2005, Oracle.  All Rights Reserved.
--
-- NAME
--   glogin.sql
--
-- DESCRIPTION
--   SQL*Plus global login "site profile" file
--
--   Add any SQL*Plus commands here that are to be executed when a
--   user starts SQL*Plus, or uses the SQL*Plus CONNECT command.
--
-- USAGE
--   This script is automatically run
-- SET Commit on Exit off
SET EXITCOMMIT OFF

Save and exit