Another SQL*Plus thing I learned...
In the past, when you exit SQL*Plus, it would always commit - there was an implicit "commit work" issued for you right before it would disconnect your session.
It has always been that way. It doesn't have to be that way anymore as of 11g Release 2.
There is a new exitcommit setting - it defaults to ON which is the way it has always worked. But now you can set it to OFF which implies that a rollback will be issued instead of commit.
Things change over time... If you rely on SQL*Plus committing upon exit - if you have a script that relies on that, bear in mind it is no longer "a fact", it is highly probable that SQL*Plus will commit when you exit - but not a sure thing like it used to be. Yet another new thing I learned while updating the book Expert Oracle Database Architecture. I was just reminded of it today while I was going over the proofs of the chapters...
See http://download.oracle.com/docs/cd/E11882_01/server.112/e10823/ch_twelve040.htm#BABEGEGC for details on exitcommit.
It has always been that way. It doesn't have to be that way anymore as of 11g Release 2.
There is a new exitcommit setting - it defaults to ON which is the way it has always worked. But now you can set it to OFF which implies that a rollback will be issued instead of commit.
Things change over time... If you rely on SQL*Plus committing upon exit - if you have a script that relies on that, bear in mind it is no longer "a fact", it is highly probable that SQL*Plus will commit when you exit - but not a sure thing like it used to be. Yet another new thing I learned while updating the book Expert Oracle Database Architecture. I was just reminded of it today while I was going over the proofs of the chapters...
See http://download.oracle.com/docs/cd/E11882_01/server.112/e10823/ch_twelve040.htm#BABEGEGC for details on exitcommit.
posted by Thomas Kyte at 4:22 PM
10 Comments:
Worth highlighting that the setting isn't applicable for a DISCONNECT (or when you use CONNECT to switch logins), and you'll still get an automatic commit from either of those operations.
http://download.oracle.com/docs/cd/E11882_01/server.112/e10823/ch_twelve020.htm#i2697902
http://download.oracle.com/docs/cd/E11882_01/server.112/e10823/ch_twelve015.htm#sthref1444
finally !
immediately added
set exitc off
to my glogin.sql
and now can start to use sqlplus also on my production db's !
The images for the sidebar ads for your books aren't showing.
So, this is great and all, but what about $ORACLE_HOME/rdbms/admin or Oracle's Grid Control Automation scripts that have relied on this setting?
We used to commonly change glogin.sql on our databases to change things like pagesize, line width, col object_name for a30.. but we started running into issues with Grid Control where it was executing sql scripts from perl and greping the output without setting the "default" sqlplus variables.
I can easily see someone setting this off in a production environment in their glogin.sql and a DBUA fails or some such.
Hi Tom
Not sure if you will come across this comment of mine.When do we expect to see you talk about design;databases ; architecture of social networking sites.
-Arnab
Hi Tom,
totally off topic but I really did not know where to post this.
It could be also highly probable you already know: you are not the only existent asktom!
http://www.guardian.co.uk/theobserver/series/asktom
Cheers.
Maurizio
@Maurizio De Giorgi...
there are many asktom's :)
http://live.washingtonpost.com/ask-tom-sietsema-052610.html is one that is local to where I live...
Rollback is more reasonable. I've been waiting for this feature for so long.
My favorite Ask Tom:
www.asktom.org
Tornadoes ace databases every time. Sorry!
I think the link has moved to http://download.oracle.com/docs/cd/E11882_01/server.112/e16604/ch_twelve040.htm
I booked marked this post a while back as I knew it would come in useful one day and today was it :0) Thank you for taking the time to post these sorts of things, they make a day to day job much easier.
POST A COMMENT
<< Home