OraFAQ Forum: SQL & PL/SQL » why DDL statements are autocommit

Home » SQL & PL/SQL » SQL & PL/SQL » why DDL statements are autocommit (oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

why DDL statements are autocommit [message #440688]

Tue, 26 January 2010 11:01

jitender.sadh
Messages: 86
Registered: May 2007

Member

Hi All,

can anybody tell why DDL statements are autocommit.

Best Regards
jitender Sadh
=============

Report message to a moderator

Re: why DDL statements are autocommit [message #440689 is a reply to message #440688]

Tue, 26 January 2010 11:02

cookiemonster
Messages: 13972
Registered: September 2008
Location: Rainy Manchester

Senior Member

Because there's no way to roll them back?

Report message to a moderator

Re: why DDL statements are autocommit [message #440691 is a reply to message #440688]

Tue, 26 January 2010 11:03

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Because DDL makes a lot a small statements on the dictionary and this one must be consistent when the statement comes back to you.
So there is a commit before, and at least one commit (or rollback) inside, just before the return.

Regards
Michel

Report message to a moderator

Re: why DDL statements are autocommit [message #440705 is a reply to message #440691]

Tue, 26 January 2010 12:17

jitender.sadh
Messages: 86
Registered: May 2007

Member

you mean query on dictionary tables.

Regards
Jitender

Report message to a moderator

Re: why DDL statements are autocommit [message #440706 is a reply to message #440705]

Tue, 26 January 2010 12:33

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Not only query, insert, update, delete and physical changes (create tablespace physically creates files...).

Regards
Michel

Report message to a moderator

Re: why DDL statements are autocommit [message #440708 is a reply to message #440688]

Tue, 26 January 2010 12:51

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

>can anybody tell why DDL statements are autocommit.
Realize Oracle must maintain a Read Consistent view for all sessions; not just the one doing the DDL.

Report message to a moderator

Re: why DDL statements are autocommit [message #440736 is a reply to message #440688]

Tue, 26 January 2010 21:52

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

if you search this before posting...i am sure you can get this..
How DDL is executed logically

Quote:

Followup August 10, 2004 - 8am Central time zone:
DDL is executed logically like this:
begin
COMMIT;
do the ddl;
COMMIT;
exception
when others then
ROLLBACK;
RAISE;
end;

sriram

Report message to a moderator

Re: why DDL statements are autocommit [message #440771 is a reply to message #440736]

Tue, 26 January 2010 23:41

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

I'm pretty sure OP knows this as he asked why it is so.

Regards
Michel

Report message to a moderator

Re: why DDL statements are autocommit [message #440777 is a reply to message #440771]

Wed, 27 January 2010 00:01

ramoradba
Messages: 2457
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...

Senior Member

if he/she knows( How DDL is executed logically) really then he/she should post this at Oracle why they are doing like that internally..... and should give more suggetions ....

@ Michel
Quote:

I'm pretty sure OP knows this as he asked why it is so.

I am not sure if op knows that or not as op did n`t stated any where in the post..

Thats why i posted that Wink

OP has to confirm that.

sriram Smile

Report message to a moderator

Re: why DDL statements are autocommit [message #440787 is a reply to message #440777]

Wed, 27 January 2010 00:43

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

I am not sure if op knows that or not as op did n`t stated any where in the post..

Then question is:
Quote:

can anybody tell why DDL statements are autocommit.

If he/she knows it autocommits, he/she knows it commits. Seems logical to me. (In addition, I already posted it.)

Regards
Michel

Report message to a moderator

Re: why DDL statements are autocommit [message #440836 is a reply to message #440688]

Wed, 27 January 2010 05:47

daverich
Messages: 23
Registered: January 2010
Location: UK

Junior Member

DDL are autocommit by design, that's how Oracle wrote their software. There is no way to change this behaviour.

[Updated on: Wed, 27 January 2010 05:50]

Report message to a moderator

Re: why DDL statements are autocommit [message #440840 is a reply to message #440836]

Wed, 27 January 2010 05:55

Michel Cadot
Messages: 68770
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Sometimes Oracle has to make choice and choose a specific design without any special reason.

For instance, SYS_CONNECT_BY_PATH returns elements from root to leaves, it could be from leaves to root, it does not matter for Oracle, it made a arbitray choice.

In this case, there are reasons and Oracle has no choice, it must commit for the reasons given above and the main is well explained by BalckSwan: Oracle has to show to all sessions a consistent view of the metadata of the database (physical and logical description of objects).

Regards
Michel

Report message to a moderator

Previous Topic:	grouping in ordinal way...
Next Topic:	Error while executing the MERGE statement (merged)

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 04 19:26:34 CDT 2025