Thursday, June 24, 2010

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.
POST A COMMENT

10 Comments:

Blogger Gary Myers said....

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

Thu Jun 24, 06:45:00 PM EDT  

Anonymous Sokrates said....

finally !

immediately added
set exitc off
to my glogin.sql
and now can start to use sqlplus also on my production db's !

Fri Jun 25, 05:37:00 AM EDT  

Anonymous Anonymous said....

The images for the sidebar ads for your books aren't showing.

Tue Jun 29, 06:42:00 AM EDT  

Blogger Curtis said....

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.

Fri Jul 02, 12:00:00 PM EDT  

Blogger Arnab Maity said....

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

Fri Jul 09, 12:23:00 PM EDT  

Blogger Maurizio De Giorgi said....

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

Wed Jul 14, 12:22:00 PM EDT  

Blogger Thomas Kyte said....

@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...

Wed Jul 14, 01:39:00 PM EDT  

Blogger MuJiang said....

Rollback is more reasonable. I've been waiting for this feature for so long.

Thu Jul 15, 06:42:00 PM EDT  

Anonymous Anonymous said....

My favorite Ask Tom:

www.asktom.org

Tornadoes ace databases every time. Sorry!

Tue Aug 17, 10:11:00 AM EDT  

Blogger Kelloggsville said....

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.

Tue Nov 23, 04:49:00 AM EST  

POST A COMMENT

<< Home