Re: Turning off DML commit when session is exited or disconnected
Date: Fri, 19 Dec 2008 14:53:57 +1100
oracle user <rcllnx_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.
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
-- tcross (at) rapttech dot com dot auReceived on Thu Dec 18 2008 - 21:53:57 CST