Beware Commit on Exit

Hi Everyone!

Here is very basic information where dba/developer tend to do mistakes.
In Oracle database each transaction ran on the session must be either committed or rolled back.
Here is very simple scenario where I have not issued commit, but Database transaction got committed.


I have created the table and inserted one row and exited the session without committing.
When I logged in back, I still see the record that I have inserted. The reason for this is 'sqlplus' utility does auto commit for you.!!

VIS:/orahome $ sqlplus '/as sysdba'

SQL*Plus: Release 9.2.0.6.0 - Production on Mon May 18 15:30:01 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> create table test_table(col1 number);

Table created.

SQL> insert into test_table values(1);

1 row created.

SQL> exit -- I have exited the sqlplus session without committing

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
VIS:/orahome $ sqlplus '/as sysdba'

SQL*Plus: Release 9.2.0.6.0 - Production on Mon May 18 15:30:46 2009

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> select * from test_table;

COL1
----------
1

SQL> exit -- Albeit, I have not explicitly committed, record is there because of auto commit.

Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
VIS:/orahome $

Make sure you rollback in case you are not intended to make any change after DML statements.