Explicit Vs Implicit Commit in Oracle– Difference and When it Occurs?

Difference between Explicit and Implicit commit:
When you submit a manual commit (Using Commit Command) after a DML (Insert, Delete, Update) statement, it is called explicit commit. Implicit commit is issued by Oracle database engine automatically after most of the DDL (alter, drop, create etc) execution. In other words, the commit does not need your interference.
If you enable auto commit enabled on your SQL*Plus or if you enabled auto commit on exit also can be considered as implicit commits.

Example:
Explicit Commit
Example#1
SQL> select * from dept;

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

SQL> Insert into dept values (50,'INFRA','ATLANTA');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from dept;

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

SQL> select * from dept;

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

SQL> delete from dept where deptno=50;

1 row deleted.

SQL> create table test1 (col1 number(2));

Table created.

SQL> rollback;

Rollback complete.

Even if you issue a rollback command after a DDL, it doesn’t make any difference as it has been committed after the DDL.

SQL>  select * from dept;

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

Example#3
Implicit commit after an unsuccessful DDL

SQL>  select * from dept;

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

SQL>  delete from dept where deptno =50;

1 row deleted.

SQL> create view dept_vw as select deptno, location from dept;
create view dept_vw as select deptno, location from dept
                                      *
ERROR at line 1:
ORA-00904: "LOCATION": invalid identifier


SQL> rollback;

Rollback complete.

SQL>  select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 FINANCE        NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
Example#4
In some cases implicit commit not happens after unsuccessful transaction. See example below.

SQL> select * from dept;

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

SQL> delete from dept where deptno=50;

1 row deleted.

SQL> create table test1 ();
create table test1 ()
                    *
ERROR at line 1:
ORA-00904: : invalid identifier


SQL> rollback;

Rollback complete.

SQL> select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 FINANCE        NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
        50 INFRA          ATLANTA
Example#5
Implicit commit with SQL*plus- autocommit on

SQL> set autocommit on
SQL> Insert into dept values (50,'INFRA','ATLANTA');

1 row created.

Commit complete.
SQL> select * from dept;

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

SQL> delete from dept where deptno =50;

1 row deleted.

Commit complete.
SQL>  select * from dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 FINANCE        NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
Example#6
In Oracle 11g R2 One more implicit commit method is introduced. set exitc[omit] on/off. By default the exitcommit is on as in prior Oracle versions and you can make it off in 11g. In prior oracle versions this option is not available. If you set exitcommit off, the transaction get rollback on the SQL*Plus exit.
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

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