|
Replies:
33
-
Pages:
3
[
1
2
3
| Next
]
-
Last Post:
Feb 11, 2010 1:07 AM
by: Norm [TeamT]
|
|
|
|
|
|
|
How can I turn on AUTOCOMMIT?
Posted:
Feb 9, 2010 11:03 AM
|
|
|
In the status bar TOAD tells me "AUTOCOMMIT OFF".
How can I turn it on?
Ben
--
View this message in context: http://old.nabble.com/How-can-I-turn-on-AUTOCOMMIT--tp27520353p27520353.html
Sent from the TOAD mailing list archive at Nabble.com.
|
|
|
Posts:
356
Registered:
10/30/09
|
|
|
|
RE: How can I turn on AUTOCOMMIT?
Posted:
Feb 9, 2010 11:11 AM
in response to: BenXS
|
|
|
Options -> Oracle -> Transactions -> Commit after every statement.
|
|
|
Posts:
451
Registered:
8/10/06
|
|
|
Posts:
17
Registered:
10/23/07
|
|
|
|
RE: How can I turn on AUTOCOMMIT?
Posted:
Feb 9, 2010 11:22 AM
in response to: BenXS
|
|
|
Ben,
Autocommit is an sql plus command. IT is generally used during script execution.
You can do the following
Set autocommit on
Or set autocommit off
In your script. Check the options window (type commit in the search box in the
lower left). Toad has a few commit options as well.
chris
|
|
|
|
|
|
|
Re: How can I turn on AUTOCOMMIT?
Posted:
Feb 9, 2010 11:27 AM
in response to: BenXS
|
|
|
Hey Ben,
> In the status bar TOAD tells me "AUTOCOMMIT OFF".
>
> How can I turn it on?
The goal of my email isn't to tell you how to turn it on, but to emboss it
into your memory so that when this option comes back to bite you, you'll
remember me. Just years of paranoia and having twice been put through a
once-in-a-lifetime ringer of bad lack of forethought. Now, about your
question:
You don't mention the version of Toad you're using, but View->Toad
Options->Oracle->Transactions->"Commit after every statement" (or search for
"commit" in the options) toggle should do what you're looking for.
Best of luck!
Rich -- [TeamT]
Disclaimer: The best tool a shop teacher has is his remaining 9 fingers.
|
|
|
Posts:
451
Registered:
8/10/06
|
|
|
|
RE: How can I turn on AUTOCOMMIT?
Posted:
Feb 9, 2010 11:28 AM
in response to: Rich Jesse
|
|
|
Or in other words, just because you can, doesn’t mean you should.
|
Follow me on twitter @hillbillyToad
Check out my blog on ToadWorld
|
|
|
|
|
|
RE: How can I turn on AUTOCOMMIT?
Posted:
Feb 9, 2010 12:19 PM
in response to: HillbillyToad
|
|
|
What Jeff and Rich are saying, I think, is that committing after every statement
is usually a VERY BAD idea.
Commits should be done based on the real transactions you're working with. They
should be done when it logically makes sense, not after every statement.
Consider a real world example of an ATM.
First SQL statement: withdraw $100 from my account (something like "insert into
financial_trans (account, transaction_amount) values (myAccountNumber,-100);
Second SQL statement: deliver $100 to the user at the ATM
If you autocommit after the first and then the second one fails, the money
leaves your account and you get nothing.
If you commit after the second succeeds, then the money gets transferred right.
And if there's a failure at the ATM or in the network, you simply roll back the
transaction.
Database work typically involves a bunch of individual statements grouped into
logical transactions. Committing after every statement is almost always bad
design. And, as Rich said, it will eventually bite you.
I've been bitten too. So I recommend leaving autocommit turned off.
-- jim
|
|
|
|
|
|
|
RE: How can I turn on AUTOCOMMIT?
Posted:
Feb 9, 2010 12:36 PM
in response to: Hudson, James -...
|
|
|
> I've been bitten too. So I recommend leaving autocommit turned off.
>
> -- jim
I should start a new group on Facebook called "Transactionally Paranoid
Cheeseheads". ;D
Rich -- [TeamTPC]
Disclaimer: I must have missed the Toad for Oracle Superbowl™ commercial
|
|
|
Posts:
323
Registered:
2/4/09
|
|
|
|
RE: How can I turn on AUTOCOMMIT?
Posted:
Feb 10, 2010 1:56 AM
in response to: Hudson, James -...
|
|
|
Morning all,
late to the party as usual! I blame time zones!
>> What Jeff and Rich are saying, I think, is that committing
>> after every statement is usually a VERY BAD idea.
Oh yes indeedy, a very very bad thing. If you do this, you *will*
[eventually] end up with a trashed database.
In fact, as an enhancement request, I'd suggest that the option be
dropped completely in future versions of Toad to avoid the distress that
comes with a commit half way through a script that means when it goes
wrong, the rollback is only partial - in other words, back to an unknown
point in the transaction rather than undoing the whole transaction.
I wonder how many people out there in Toad Land actually turn autocommit
on?
The example I much prefer is a similar one to the banking analogy given
earlier:
You wish to transfer $100 from your savings account to your cheque
account:
UPDATE SAVINGS ACCOUNT
SET TOTAL = TOTAL - 100;
[AUTO COMMIT]
UPDATE CHEQUE ACCOUNT
SET TOTAL = TOTAL + 100;
[AUTO COMMIT]
As was pointed out earlier, if it works, fine. If it fails after
updating the savings account, it loses you $100 and the bank gains $100
as it has no idea where it came from - finders keepers. Now you might
think that the bank would do this instead:
UPDATE CHEQUE ACCOUNT
SET TOTAL = TOTAL + 100;
[AUTO COMMIT]
UPDATE SAVINGS ACCOUNT
SET TOTAL = TOTAL - 100;
[AUTO COMMIT]
I wonder if you can think of a reason why they wouldn't! 
Without autocommit it's fine and dandy whichever way around it works:
UPDATE CHEQUE ACCOUNT
SET TOTAL = TOTAL + 100;
UPDATE SAVINGS ACCOUNT
SET TOTAL = TOTAL - 100;
COMMIT;
Now, if it fails at any point in the transaction, your money is safe and
so is the bank's.
Remember, a transaction is a logically complete update that must succeed
or fail as a single unit.
Autocommit - just say NO!
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
|
|
|
Posts:
16
Registered:
11/19/09
|
|
|
|
Re: How can I turn on AUTOCOMMIT?
Posted:
Feb 10, 2010 2:11 AM
in response to: Norm [TeamT]
|
|
|
There is one form of 'autocommit' that is very hard to turn off.
We regularly receive sql-scripts to run that contain a mix of DML- and
DDL-statements.
But still the single commit on the last line! 
Peter
Op 10-02-2010 10:56, Dunbar, Norman schreef:
>
>
> Morning all,
>
> late to the party as usual! I blame time zones!
>
> >> What Jeff and Rich are saying, I think, is that committing
> >> after every statement is usually a VERY BAD idea.
> Oh yes indeedy, a very very bad thing. If you do this, you *will*
> [eventually] end up with a trashed database.
>
> In fact, as an enhancement request, I'd suggest that the option be
> dropped completely in future versions of Toad to avoid the distress that
> comes with a commit half way through a script that means when it goes
> wrong, the rollback is only partial - in other words, back to an unknown
> point in the transaction rather than undoing the whole transaction.
>
> I wonder how many people out there in Toad Land actually turn autocommit
> on?
>
> The example I much prefer is a similar one to the banking analogy given
> earlier:
>
> You wish to transfer $100 from your savings account to your cheque
> account:
>
> UPDATE SAVINGS ACCOUNT
> SET TOTAL = TOTAL - 100;
> [AUTO COMMIT]
>
> UPDATE CHEQUE ACCOUNT
> SET TOTAL = TOTAL + 100;
> [AUTO COMMIT]
>
> As was pointed out earlier, if it works, fine. If it fails after
> updating the savings account, it loses you $100 and the bank gains $100
> as it has no idea where it came from - finders keepers. Now you might
> think that the bank would do this instead:
>
> UPDATE CHEQUE ACCOUNT
> SET TOTAL = TOTAL + 100;
> [AUTO COMMIT]
>
> UPDATE SAVINGS ACCOUNT
> SET TOTAL = TOTAL - 100;
> [AUTO COMMIT]
>
> I wonder if you can think of a reason why they wouldn't!
>
> Without autocommit it's fine and dandy whichever way around it works:
>
> UPDATE CHEQUE ACCOUNT
> SET TOTAL = TOTAL + 100;
>
> UPDATE SAVINGS ACCOUNT
> SET TOTAL = TOTAL - 100;
>
> COMMIT;
>
> Now, if it fails at any point in the transaction, your money is safe and
> so is the bank's.
>
> Remember, a transaction is a logically complete update that must succeed
> or fail as a single unit.
>
> Autocommit - just say NO!
>
> Cheers,
> Norm. [TeamT]
>
> Information in this message may be confidential and may be legally
> privileged. If you have received this message by mistake, please notify
> the sender immediately, delete it and do not copy it to anyone else. We
> have checked this email and its attachments for viruses. But you should
> still check any attachment before opening it. We may have to make this
> message and any reply to it public if asked to under the Freedom of
> Information Act, Data Protection Act or for litigation. Email messages
> and attachments sent to or from any Environment Agency address may also
> be accessed by someone other than the sender or recipient, for business
> purposes. If we have sent you information and you wish to use it please
> read our terms and conditions which you can get by calling us on 08708
> 506 506. Find out more about the Environment Agency at
> www.environment-agency.gov.uk
>
> Information in this message may be confidential and may be legally
> privileged. If you have received this message by mistake, please notify
> the sender immediately, delete it and do not copy it to anyone else.
>
> We have checked this email and its attachments for viruses. But you
> should still check any attachment before opening it.
> We may have to make this message and any reply to it public if asked to
> under the Freedom of Information Act, Data Protection Act or for
> litigation. Email messages and attachments sent to or from any
> Environment Agency address may also be accessed by someone other than
> the sender or recipient, for business purposes.
>
> If we have sent you information and you wish to use it please read our
> terms and conditions which you can get by calling us on 08708 506 506.
> Find out more about the Environment Agency at www.environment-agency.gov.uk
>
>
|
|
|
Posts:
323
Registered:
2/4/09
|
|
|
|
RE: How can I turn on AUTOCOMMIT?
Posted:
Feb 10, 2010 2:22 AM
in response to: Peter van Rijn
|
|
|
Morning Peter,
>> There is one form of 'autocommit' that is very hard to turn off.
>>
>> We regularly receive sql-scripts to run that contain a mix
>> of DML- and DDL-statements.
Yes, we used to get those from vendors as well. However, I created a set
of scripting standards, had it approved and all our vendors now have a
copy - and get any updates as and when required - and they have been
told that any scripts that they supply which do not adhere to the
scripting standards will be rejected when a DBA QAs the script(s) before
application.
Once a couple of scripts have been rejected and thrown back for
correction, they soon wise up.
>> But still the single commit on the last line!
Glad it's not just us then! The use of a commit on the final line of a
DDL script shows a severe lack of understanding about exactly how Oracle
works - in fact, mixing DML and DDL shows this too. Our standards demand
that vendors do not put a COMMIT or ROLLBACK into any script.
They do put a prompt at the end telling the DBA that they should commit
if no errors occurred otherwise, rollback.
After all, how does the script writer know that our database has, for
example, got enough UNDO to cope with what turns out to be a huge
transaction generating lots and lots of UNDO and which blows away all of
the UNDO - then commits.
As we stipulate, the DBA applying the script(s) is the only one who
dictates whether a commit or rollback is required and appropriate. Which
means, also, that the script better have a spool <filename> at the
start and spool off at the end - of it fails QA as well!
Cheers,
Norm. [TeamT]
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else. We have checked this email and its attachments for viruses. But you should still check any attachment before opening it. We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes. If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
Information in this message may be confidential and may be legally privileged. If you have received this message by mistake, please notify the sender immediately, delete it and do not copy it to anyone else.
We have checked this email and its attachments for viruses. But you should still check any attachment before opening it.
We may have to make this message and any reply to it public if asked to under the Freedom of Information Act, Data Protection Act or for litigation. Email messages and attachments sent to or from any Environment Agency address may also be accessed by someone other than the sender or recipient, for business purposes.
If we have sent you information and you wish to use it please read our terms and conditions which you can get by calling us on 08708 506 506. Find out more about the Environment Agency at www.environment-agency.gov.uk
|
|
|
Posts:
16
Registered:
11/19/09
|
|
|
|
Re: How can I turn on AUTOCOMMIT?
Posted:
Feb 10, 2010 2:31 AM
in response to: Norm [TeamT]
|
|
|
Op 10-02-2010 11:22, Dunbar, Norman schreef:
>
>
> dictates whether a commit or rollback is required and appropriate. Which
> means, also, that the script better have a spool <filename> at the
> start and spool off at the end - of it fails QA as well!
>
> Cheers,
> Norm. [TeamT]
We're drifting a bit too off-topic I'm afraid, but talking about
spooling, I don't know if your standards mention spooling as well.
In the past we used an application and received vendor supplied scripts
every once in a while. They always spooled to "C:\temp\<whatever.lst>".
very nice when running the scripts on a unix-box.
Well, enough ranting for today.
cheerio
Peter
|
|
|
Posts:
56
Registered:
11/18/09
|
|
|
|
Re: How can I turn on AUTOCOMMIT?
Posted:
Feb 10, 2010 3:48 AM
in response to: Norm [TeamT]
|
|
|
|
|
|
Posts:
291
Registered:
5/19/06
|
|
|
|
RE: How can I turn on AUTOCOMMIT?
Posted:
Feb 10, 2010 4:54 AM
in response to: Peter van Rijn
|
|
|
Oracle provides a command for this CREATE SCHEMA. Basically you can wrap a collection of DDL commands and treat them as one transaction (all or one). It's been in Oracle since at least Oracle 8.i.
|
|
|
Posts:
291
Registered:
5/19/06
|
|
|
|
RE: How can I turn on AUTOCOMMIT?
Posted:
Feb 10, 2010 5:06 AM
in response to: Peter van Rijn
|
|
|
Here's the command description from the 8i docs:
CREATE SCHEMA
Purpose
Use the CREATE SCHEMA to create multiple tables and views and perform multiple grants in a single transaction.
To execute a CREATE SCHEMA statement, Oracle executes each included statement. If all statements execute successfully, Oracle commits the transaction. If any statement results in an error, Oracle rolls back all the statements.
|
|
|
|
Legend
|
|
Guru: 2001
+
pts
|
|
Expert: 751
- 2000
pts
|
|
Enthusiast: 31
- 750
pts
|
|
Novice: 0
- 30
pts
|
|
Moderators
|
|
Helpful answer
(5 pts)
|
|
Answered
(10 pts)
|
|
