msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

SQL PLUS Gotcha

While reading the oracle-l mailing list, someone asked a question about (auto)commiting transactions in SQL Plus. Consider:

scott@eddev> show autocommit
autocommit OFF
scott@eddev>

The above shows that autocommit is off, the default when you connect to SQL Plus. It means that automatic committing is suppressed. You must commit changes manually with the SQL command commit.

scott@eddev> select count(*) from t;

  COUNT(*)
----------
     23495

scott@eddev> delete t;

23495 rows deleted.

scott@eddev> select count(*) from t;

  COUNT(*)
----------
         0

scott@eddev> exit;
Disconnected from Oracle9i Enterprise 
Edition Release 9.2.0.1.0 - Production

So, while autocommit was off, we deleted all records from the table t. We exited SQL Plus without committing. Since we did not commit, the 23495 records should be rolled back or “un-deleted” from table t, right? Let’s connect again and see:

H:\SQLplus>sqlplus scott/tiger

SQL*Plus: Release 9.2.0.1.0 - 
Production on Mon Aug 15 13:32:10 2005

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


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

scott@eddev> select count(*) from t;

  COUNT(*)
----------
         0

OOPS! table t is empty despite not committing our “delete” in the first session. Why is that? That’s because, based on the documentation, exit commits all pending changes by default, logs out of Oracle, terminates SQL Plus and returns control to the operating system. If we wanted to exit SQL Plus without committing, we should have either issued a rollback then an exit or just simply exit rollback. Note that both SQL Plus commands exit and quit are identical and have the same behavior.

Next time, when you exit SQL Plus, you better think twice :)

Related links:
Oracle-l mailing list
Documentation


Filed in Oracle on 15 Aug 05 | Tags: ,


Reader's Comments

  1. |

    Yes! SQL*Plus has so many quirks.

    If you use default settings, and you are not committing manually, you are getting your own personal, private view – until you exit!

    Despite the fact that this has caught almost everybody at least once, it’s a rather obscure “gotcha” to find on any FAQ or blog. Until now :)

    In the SQL*Plus User’s Guide, see “Saving Changes to the Database Automatically”, and “Exit” in the SQL*Plus Command Reference. Even then, it’s easy to miss.

    For version 9, get the doc here.

  2. |

    It’s these little things that bite you sometimes, and there are a lot of them.