exitcommit

There is a new SQL*Plus system variable in Oracle 11.2 called exitcommit. The default is on but, if you set it to off, Oracle does not commit pending transactions when you exit. In the first example, some rows are inserted into a table with exitcommit set to on before typing exit to return to the OS:

 

SQL> show exitcommit

exitcommit ON

SQL> create table andrew1 as

  2  select * from dba_tables

  3  where 1=2

  4  /

 

Table created.

 

SQL> insert into andrew1

  2  select * from dba_tables

  3  /

 

3061 rows created.

 

SQL> select count(*) from andrew1

  2  /

 

  COUNT(*)

----------

      3061

 

SQL> exit

 

In the second example, exitcommit is set to off:

 

SQL> set exitcommit off

SQL> show exitcommit

exitcommit OFF

SQL> create table andrew2 as

  2  select * from dba_tables

  3  where 1=2

  4  /

 

Table created.

 

SQL> insert into andrew2

  2  select * from dba_tables

  3  /

 

3062 rows created.

 

SQL> select count(*) from andrew2

  2  /

 

  COUNT(*)

----------

      3062

 

SQL> exit

 

When you look at the tables afterwards, the second one is empty:

 

SQL> select count(*) from andrew1

  2  /

 

  COUNT(*)

----------

      3061

 

SQL> select count(*) from andrew2

  2  /

 

  COUNT(*)

----------

         0

 

SQL>