advertisements
_____________________________________________________________________________________________________________________
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.
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.
_____________________________________________________________________________________________________________________
0 comments:
Post a Comment