Saturday, April 21, 2012

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>

No comments: