Oracle 11gR2 SQLPlus SET EXITCOMMIT ON/OFF

EXITONCOMMIT is a new set option with Oracle 11g R2 SQL*Plus. Prior to 11gR2, auto commit is on for all other version. But, in release 2 you can set whether you want autocommit during the SQL*Plus exit.

Example
SQL>  select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 FINANCE        NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 INFRA          ATLANTA
SQL> show exitcommit
exitcommit ON
SQL> set exitcommit off

SQL> show exitcommit
exitcommit OFF

SQL> delete from dept where deptno=50;

1 row deleted.

SQL> exit;

$sqlplus scott/tiger
SQL>  select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 FINANCE        NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 INFRA          ATLANTA

It was not committed during last exit because of exitcommit set as off.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google