Home » SQL & PL/SQL » SQL & PL/SQL » why DDL statements are autocommit (oracle 10g)
why DDL statements are autocommit [message #440688] Tue, 26 January 2010 11:01 Go to next message
jitender.sadh
Messages: 82
Registered: May 2007
Member
Hi All,

can anybody tell why DDL statements are autocommit.


Best Regards
jitender Sadh
=============
Re: why DDL statements are autocommit [message #440689 is a reply to message #440688] Tue, 26 January 2010 11:02 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Because there's no way to roll them back?
Re: why DDL statements are autocommit [message #440691 is a reply to message #440688] Tue, 26 January 2010 11:03 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: why DDL statements are autocommit [message #440705 is a reply to message #440691] Tue, 26 January 2010 12:17 Go to previous messageGo to next message
jitender.sadh
Messages: 82
Registered: May 2007
Member
you mean query on dictionary tables.

Regards
Jitender
Re: why DDL statements are autocommit [message #440706 is a reply to message #440705] Tue, 26 January 2010 12:33 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Not only query, insert, update, delete and physical changes (create tablespace physically creates files...).

Regards
Michel
Re: why DDL statements are autocommit [message #440708 is a reply to message #440688] Tue, 26 January 2010 12:51 Go to previous messageGo to next message
BlackSwan
Messages: 24912
Registered: January 2009
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.
Re: why DDL statements are autocommit [message #440736 is a reply to message #440688] Tue, 26 January 2010 21:52 Go to previous messageGo to next message
ramoradba
Messages: 2454
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 Smile
Re: why DDL statements are autocommit [message #440771 is a reply to message #440736] Tue, 26 January 2010 23:41 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I'm pretty sure OP knows this as he asked why it is so.

Regards
Michel
Re: why DDL statements are autocommit [message #440777 is a reply to message #440771] Wed, 27 January 2010 00:01 Go to previous messageGo to next message
ramoradba
Messages: 2454
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
Re: why DDL statements are autocommit [message #440787 is a reply to message #440777] Wed, 27 January 2010 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: why DDL statements are autocommit [message #440836 is a reply to message #440688] Wed, 27 January 2010 05:47 Go to previous messageGo to next message
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 Go to previous message
Michel Cadot
Messages: 63808
Registered: March 2007
Location: Nanterre, France, http://...
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
Previous Topic: grouping in ordinal way...
Next Topic: Error while executing the MERGE statement (merged)
Goto Forum:
  


Current Time: Wed Sep 28 19:26:14 CDT 2016

Total time taken to generate the page: 0.05526 seconds