Re: Turning off DML commit when session is exited or disconnected
- From: Cristian Cudizio <cristian.cudizio@xxxxxxxx>
- Date: Fri, 19 Dec 2008 00:10:32 -0800 (PST)
On 19 Dic, 04:53, Tim X <t...@xxxxxxxxxxxxxxx> wrote:
oracle user <rcl...@xxxxxxxxx> 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
.
- References:
- Prev by Date: Re: performance of updates
- Next by Date: Re: tool: convert classic oracle sql to ansi?
- Previous by thread: Re: Turning off DML commit when session is exited or disconnected
- Next by thread: performance of updates
- Index(es):
Relevant Pages
|
