Turning off DML commit when session is exited or disconnected
oracle user
(where autocommit is off by default ) and then 'exit' or 'disc' the
session without explicitly commiting the DML ,the DML gets commited .
Is it possible to turn off this default behaviour.
Thanks in Advance
sybrandb
It is NOT possible.
How would it be? By design, your transaction ends by exiting Oracle.
It is a feature of the database, not of sql*plus.
If you don't want to commit your transaction, you issue rollback.
This won't get you RSI.
--
Sybrand Bakker
Senior Oracle DBA
ddf
On Dec 18, 5:28 am, oracle user <rcl...@gmail.com> wrote:
> If i issue a DML statment in oracle database from sqlplus session
> (where autocommit is off by default ) and then 'exit' or 'disc' the
> session without explicitly commiting the DML ,the DML gets commited .
Sorry, you DO commit the DML as, by default, an 'exit' is actually an
'exit commit'. You do have the option to execute an 'exit rollback'
instead:
SQL> --
SQL> -- Create a test table
SQL> --
SQL> create table mytest (mykey number,
2 myval varchar2(40),
3 constraint mytest_pk
4 primary key(mykey)
5 );
Table created.
SQL>
SQL> --
SQL> -- Insert data we don't want to preserve
SQL> --
SQL> insert all
2 into mytest
3 values (1, 'Test 1')
4 into mytest
5 values (2, 'Test 2')
6 into mytest
7 values (3, 'Test 3')
8 into mytest
9 values (4, 'Test 4')
10 into mytest
11 values (5, 'Test 5')
12 select *
13 from dual;
5 rows created.
SQL>
SQL> --
SQL> -- Prove the insert succeeded
SQL> --
SQL> select * from mytest;
MYKEY MYVAL
---------- ----------------------------------------
1 Test 1
2 Test 2
3 Test 3
4 Test 4
5 Test 5
SQL>
SQL> --
SQL> -- Exit session and discard data changes
SQL> --
SQL> exit rollback
Disconnected from Oracle Database 11g Enterprise Edition Release
11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing
options
c:\sql\orcl\examples>sqlplus /nolog
SQL*Plus: Release 11.1.0.6.0 - Production on Thu Dec 18 08:02:51 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
SQL> connect #######/^^^^^^^^^^^^^^^^^
Connected.
SQL> set echo on
SQL> @rollback_on_exit_2
SQL> --
SQL> -- Verify the inserts from the previous
SQL> -- session weren't preserved by the 'exit'
SQL> --
SQL> select * from mytest;
no rows selected
SQL>
> Is it possible to turn off this default behaviour.
Sort of. See above.
> Thanks in Advance
David Fitzjarrell
Cristian Cudizio
Well, i've to admit that this for me is a surprise. I've always used
explicit commit or rollback
but i've never knew that exiting or disconnecting from sqlplus
transaction gets automatically
committed. It is true that this behaviour cannot be changed, but it
is a feature
of SQL*Plus (http://download.oracle.com/docs/cd/B19306_01/server.102/
b14357/ch12023.htm#i2697968)
If you kill a session, oracle rollbacks any changes.
Regards,
Cristian
Jeremy
@r36g2000prf.googlegroups.com>, rcl...@gmail.com says...>
in sqlplus:
SQL> set autocommit off
?
--
jeremy
sybrandb
>
> - Tekst uit oorspronkelijk bericht weergeven -
Cristian,
It is NOT a feature of sql*plus. It is a feature of Oracle.
It applies to anything connecting to Oracle, leaving with a proper
exit command.
DA Morgan
It is best to test advice before giving it. <g>
In sqlplus, not sqlplusw, autocommit is off by default.
Closing the executable commits uncommited transactions.
You have misunderstood the autocommit parameter.
--
Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington
damo...@x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Jeremy
says...>
> > In article <619d22a2-cb7a-4d1d-a889-11e4a9014d58
> > @r36g2000prf.googlegroups.com>, rcl...@gmail.com says...>
> >> If i issue a DML statment in oracle database from sqlplus session
> >> (where autocommit is off by default ) and then 'exit' or 'disc' the
> >> session without explicitly commiting the DML ,the DML gets commited .
> >> Is it possible to turn off this default behaviour.
> >> Thanks in Advance
> >
> > in sqlplus:
> >
> > SQL> set autocommit off
> >
> > ?
>
> It is best to test advice before giving it. <g>
>
> In sqlplus, not sqlplusw, autocommit is off by default.
> Closing the executable commits uncommited transactions.
> You have misunderstood the autocommit parameter.
It appears so.
<goes to make a coffee>
--
jeremy
Cristian Cudizio
Thanks Sybrand, you are right, i've confused closing a session with
recovering a crashed session. I've seen that also jdbc drivers on
closing
the connection do an implicit commit
http://download.oracle.com/docs/cd/B19306_01/java.102/b14355/basic.htm#sthref74
Regards,
Cristian
Tim X
Just to clarify....
If your session is terminated through what would be considered 'normal'
or explicit behavior, such as issuing an exit, an implicit commit is
executed. However, if your session is lost due to an abnormal
termination/exit, such as losing your network connection, then I believe an implicit rollback occurs.
This is quite sane and I think acceptable behavior. If you are
explicitly exiting a session, then you should do a rollback if you don't
want your changes committed. On the other hand, if your session is lost
part way through execution, then you want the system to perform a
rollback so that the system is left in a known state where all
transaction groups have completed.
Although I've not checked, it may be possible to mimic the bahavior your
after with session triggers, but I'd avoid doing that as other users
unaware of your changes who expect the default action may get
burnt. Rule of thumb, don't mess with Oracle's normal behavior with
respect to commits and rollbacks - instead, adjust how you work to fit
with how it works. Once you start changing things here, you will end up
with all sorts of user confusion - its bad enough that many developers
seem to have trouble remembering that DDL causes an implicit commit or
even understanding what is and is not a DDL action. Best not to make
things potentially more muddy.
Why do you feel you need to disable the implicit commit on a normal
session exit rather than relying on the user to do the right thing based
on whatever they have done in that session? Maybe there is a different
solution to your problem
Tim
--
tcross (at) rapttech dot com dot au
Cristian Cudizio
> oracle user <rcl...@gmail.com> writes:
> > If i issue a DML statment in oracle database from sqlplus session
> > (where autocommit is off by default ) and then 'exit' or 'disc' the
> > session without explicitly commiting the DML ,the DML gets commited .
> > Is it possible to turn off this default behaviour.
> > Thanks in Advance
>
> Just to clarify....
>
> If your session is terminated through what would be considered 'normal'
> or explicit behavior, such as issuing an exit, an implicit commit is
> executed. However, if your session is lost due to an abnormal
> termination/exit, such as losing your network connection, then I believe an implicit rollback occurs.
If there is a abnormal closing of network connection Oracle by default
makes nothing, so if there are pending transactions there must be
a manual intervention by a DBA to kill the session to release locks.
Yet
in this situation the session can stay with status "KILLED".
You have to use RESORCE MANAGER (but you can only if you have
Enterprise Edition) or specify SQLNET.EXPIRE_TIME
(http://download.oracle.com/docs/cd/B19306_01/network.102/b14213/
sqlnet.htm#sthref474)
>
> This is quite sane and I think acceptable behavior. If you are
> explicitly exiting a session, then you should do a rollback if you don't
> want your changes committed. On the other hand, if your session is lost
> part way through execution, then you want the system to perform a
> rollback so that the system is left in a known state where all
> transaction groups have completed.
>
> Although I've not checked, it may be possible to mimic the bahavior your
> after with session triggers, but I'd avoid doing that as other users
> unaware of your changes who expect the default action may get
> burnt. Rule of thumb, don't mess with Oracle's normal behavior with
> respect to commits and rollbacks - instead, adjust how you work to fit
> with how it works. Once you start changing things here, you will end up
> with all sorts of user confusion - its bad enough that many developers
> seem to have trouble remembering that DDL causes an implicit commit or
> even understanding what is and is not a DDL action. Best not to make
> things potentially more muddy.
>
> Why do you feel you need to disable the implicit commit on a normal
> session exit rather than relying on the user to do the right thing based
> on whatever they have done in that session? Maybe there is a different
> solution to your problem
>
> Tim
>
> --
> tcross (at) rapttech dot com dot au
Regards,
Cristian