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
On 18 dec, 12:28, 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 . > Is it possible to turn off this default behaviour. > Thanks in Advance
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.
Comments embedded. 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> -- 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.
> On 18 dec, 12:28, 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 . > > Is it possible to turn off this default behaviour. > > Thanks in Advance
> 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
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.
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
> On 18 Dic, 14:04, sybrandb <sybra...@gmail.com> wrote:
> > On 18 dec, 12:28, 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 . > > > Is it possible to turn off this default behaviour. > > > Thanks in Advance
> > 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
> 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- Tekst uit oorspronkelijk bericht niet weergeven -
> - 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.
Jeremy wrote: > 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. -- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damor...@x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.org
> Jeremy wrote: > > 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.
> On 18 dec, 15:12, Cristian Cudizio <cristian.cudi...@yahoo.it> wrote:
> > On 18 Dic, 14:04, sybrandb <sybra...@gmail.com> wrote:
> > > On 18 dec, 12:28, 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 . > > > > Is it possible to turn off this default behaviour. > > > > Thanks in Advance
> > > 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.
> > 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- Tekst uit oorspronkelijk bericht niet weergeven -
> > - 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.
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.
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
On 19 Dic, 04:53, Tim X <t...@nospam.dev.null> wrote:
> 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
|
