RE: sql*plus autocommit and exit

A transaction is demarkated by savepoints or commit or rollback not by
statements.  Autocommit causes a commit to be issued after every
statement. (ugh)  
So commiting after N trasactions is pointless.  Comiting after N
statements is a danger to trnasaction boundries and data consistency.
Jim

________________________________

From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Freeman, Donald
Sent: Monday, August 15, 2005 12:27 PM
To: dba.orcl@xxxxxxxxx; oracle-l
Subject: RE: sql*plus autocommit and exit


That's not what autocommit does.  You can set autocommit n to some value
and it will commit after each 200 transactions. If you don't enter a
value it will commit after each transaction.  Doesn't have anything to
do with commit before logout.   It looks like what is happening is an
implicit commit because you logged off.  
 
See item 3 below: 
Oracle9i Database Concepts
Release 2 (9.2)

A transaction ends when any of the following occurs: 

*       You issue a COMMIT or ROLLBACK (without a SAVEPOINT clause)
statement. 
*       You execute a DDL statement (such as CREATE, DROP, RENAME,
ALTER). If the current transaction contains any DML statements, Oracle
first commits the transaction, and then executes and commits the DDL
statement as a new, single statement transaction. 
*       A user disconnects from Oracle. (The current transaction is
committed.) 
*       A user process terminates abnormally. (The current transaction
is rolled back.) 

 

Don Freeman 
Database Administrator 1 
Bureau of Information Technology 
Pennsylvania Department of Health 
717-783-8095 Ext 337 

        -----Original Message-----
        From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Sami Seerangan
        Sent: Monday, August 15, 2005 2:33 PM
        To: oracle-l
        Subject: sql*plus autocommit and exit
        
        
        Hi:
        
        I have a autocommit value set to OFF however when I exit from
SQL*Plus it commits the transaction.
        Is it a expected behavior?
        
        SQL> show auto
        autocommit OFF
        
        SQL> delete from t1;
        48601 rows deleted.
        
        SQL> exit
        Disconnected from Oracle Database 10g Enterprise Edition Release
10.1.0.2.0 - Production
        With the Partitioning, OLAP and Data Mining options
        C:\Documents and Settings\s397131\Desktop>sqlplus sami/sami
        SQL*Plus: Release 10.1.0.2.0 - Production on Mon Aug 15 14:29:05
2005
        Copyright (c) 1982, 2004, Oracle.  All rights reserved.
        
        Connected to:
        Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 -
Production
        With the Partitioning, OLAP and Data Mining options
        
        SQL> select count(*) from t1;
        
          COUNT(*)
        ----------
                 0
        
        SQL> show auto
        autocommit OFF
        SQL>

Other related posts: