Re: Turning off DML commit when session is exited or disconnected

From: Cristian Cudizio <cristian.cudizio_at_yahoo.it>
Date: Fri, 19 Dec 2008 00:10:32 -0800 (PST)
Message-ID: <d766743f-3412-4e53-bfed-6852b273d807@r34g2000vbp.googlegroups.com>


On 19 Dic, 04:53, Tim X <t..._at_nospam.dev.null> wrote:
> oracle user <rcl..._at_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 Received on Fri Dec 19 2008 - 02:10:32 CST

Original text of this message