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.

4 comments:

Siva said...

Hi Suresh,

Well I think, we can setup our login profile i.e. in login.sql set autocommit off;

But as you said, its by default on and hence an issue if we dont want to save the data.

Anyways nice posting..

Thanks
Siva

Suresh Lakshmanan said...

Well said Siva.

Thanks
Suresh

Puneet Sachar said...

May be the session is in auto commit mode....

Michael Elkin said...

Setting autocommit off does not solve the problem, you can do:
exit rollback;

This performs rollback on exit.