x

Do we need a commit after DDL statements ?

Generally to save the transactions or query results in the database we have a command COMMIT, which we doesn't use generally after DDL statemnts. If anybody knows the reason then please share with me ? Thank you in advance.

more ▼

asked Jan 06, 2010 at 10:35 AM in Default

OraLearner gravatar image

OraLearner
1.3k 76 86 88

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

DDL statements are "autocommited" by design on oracle.

Tools working on oracle like sql developer offer an autocommit mode. Switching off this mode effects only dml statements. DDL statements are always autocommited.

Internal the processing is something like that:

BEGIN
   COMMIT;
   ... DDL ....
   COMMIT;
EXCEPTION
   WHEN OTHERS THEN
     ROLLBACK;
     RAISE;
END;

Thats different to sql server.

more ▼

answered Jan 06, 2010 at 06:56 PM

Christian13467 gravatar image

Christian13467
846 2

AutoCommit is a nice feature, until it bites you in the rear. Use with extreme caution in production environments - regardless of the tool you use.
Jan 06, 2010 at 08:21 PM HillbillyToad
(comments are locked)
10|1200 characters needed characters left

DDL CREATE/ALTER/DROP commands are implicitly committed.

In a session, if you do 100 inserts, 20 updates, and then 1 DROP at the end, all of that work will be committed whether you issue a COMMIT or not.

more ▼

answered Jan 06, 2010 at 11:03 AM

HillbillyToad gravatar image

HillbillyToad
1k 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x378
x50
x33
x3

asked: Jan 06, 2010 at 10:35 AM

Seen: 8731 times

Last Updated: Jan 06, 2010 at 10:35 AM