committing transaction

When and how do you commit a transaction?

SQL> insert into lsc_t(x) values(1);         ... (1) not committed
SQL> commit;                                 ... (1) committed

SQL> insert into lsc_t(x) values(2);         ... (2) not committed
SQL> alter table lsc_t disable primary key;  ... (2) committed

SQL> set autocommit ON
SQL> insert into lsc_t(x) values(3);         ... (3) committed
SQL> set autocommit OFF

SQL> insert into lsc_t(x) values(4);         ... (4) not committed
SQL> disc                                    ... (4) committed

Row 1 is committed after the commit keyword.

Row 2 is implicitely committed after before the alter table ddl statement.

Row 3 is autocommitted. Autocommit exists in sqlplus, but is more often seen in stateless applications (for instance a web application).

Row 4 is committed after a successfull disconnection.

But what appends if you kill your current session?

SQL> insert into lsc_t(x) values (5);

1 row created.

SQL> !ps
 13903 pts/33   0:00 ksh
 22384 pts/33   0:00 sqlplus

SQL> !kill 22384

The row 5 is not commited

Ref: Overview of Transaction Management

A transaction ends when any of the following occurs:
– A user issues a COMMIT or ROLLBACK statement without a SAVEPOINT clause.
– A user runs a DDL statement such as CREATE, DROP, RENAME, or ALTER (*).
If the current transaction contains any DML statements, Oracle Database
first commits the transaction, and then runs and commits the DDL statement
as a new, single statement transaction.
– A user disconnects from Oracle Database. The current transaction is committed.
– A user process terminates abnormally. The current transaction is rolled back.

(*) but not ALTER SESSION

Please read On Implicit Commit

5 Replies to “committing transaction”

  1. Hi Laurent

    I would like to precise your statement about implicit commit when executing a DDL statement.

    In fact Row 2 (from your example) is committed before the alter table statement is executed. If you execute a DDL statement, it is always surrounded with a pair of implicit commit statements.

    More details can be found in an article from Tom Kyte in the chapter DDL Locks.


  2. Oh yes, thanks for pointing this out, the row is committed even if the ddl fails, madness!

    SQL> insert into lsc_t(x) values (5);             
      --> (5) not committed
    1 row created.
    SQL> create table tttt(x number) tablespace system;
    create table tttt(x number) tablespace system
    ERROR at line 1:
    ORA-01950: no privileges on tablespace 'SYSTEM' 
      --> (5) committed

  3. Hi Laurent

    Another way to commit:
    exec dbms_stats.gather_table_stats(user,’T’);

    The documentation of dbms_stats package says:
    Most of the procedures in this package commit the current transaction, perform the operation, and then commit again.

    So don’t gather stats on temporary tables (on commit delete rows) 🙂


  4. Pingback: committing transaction | Oracle

Leave a Reply