why commit is not required for DDL Commands

PHK

Hello Everyone, Please guide me. why commit is not required for DDL commands whereas it is compulsory for DML commands to save changes permanently to the database. Thanks in advance.

jaramill

"Oracle Database implicitly commits the current transaction before and after every DDL statement.". 

See the documentation http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_1001.htm#SQLRF30001

Karthick2003

Oracle performs implicit commit before and after a DDL statement. So you don't have to perform a commit explicitly when it comes to DDL statement.

Kaleem Ahmed

DDL is auto commit and you need not to issue commit statement as it affects on structure or meta data in the database while in DML, it affects on data. That's why, DML require commit or rollback to same or revert your changes.

PHK

but my question is why ddl is auto commit but dml not???

jaramill

PHK wrote:

but my question is why ddl is auto commit but dml not???

Because DDLs are for CREATING/ALTERING/DROPPING objects and/or system/session settings and are not something you can UNDO via ROLLBACK.

Karthick2003

DDL internally performs DML operation to update the data dictionary. Oracle cannot give the transaction management to end user for such operation. So oracle maintains the transaction by itself.

Jonathan Lewis

PHK wrote:

Hello Everyone, Please guide me. why commit is not required for DDL commands whereas it is compulsory for DML commands to save changes permanently to the database. Thanks in advance.

Sometimes the answer is: "That's just the way that Oracle Corp. implemented it."

There may have been some historic difficulty with doing it differently, but once something like that has been around for a few years it can be very hard to change.

Regards

Jonathan Lewis

Prashant Dabral

DML operation if they become autocommit then how would you control a transaction, performing  a Rollback/commit.

Who will tell Oracle that a transaction have to commit or rollback, Other users/developers are working within same database they might get affected.

Its all about consistency, isolations. Check ACID property http://sql-plsql.blogspot.in/2011/04/acid-properties.html

DDL working is well explained by Karthick_Arp.

DKhatiyan

What Actually happens in case of DML(Insert,update,Delete)  undo logs are managed in case if we want to rollback the changes done, but in case of DDL command like truncate direct de-allocation of space happens and no undo logs are managed, So rollback is not required due to which it is an auto commit process.

Karthick2003

Undo is generated for DDL. If the DDL fails in the mid way due to some reason then oracle has to clean up the dictionary changes it has done. TRUNCATE is a different case. But still it also generates UNDO. Here is a nice discussion on that subject

Does Truncate Table generate Undo?

Jonathan Lewis

3008866 wrote:

but in case of DDL command like truncate direct de-allocation of space happens and no undo logs are managed, So rollback is not required due to which it is an auto commit process.
  

There's a recent lengthy thread about what undo and redo is generated that demonstrate your statements are incorrect:

You might ask yourself - what will happen to your database if a truncate statement has been running for several minutes, so you interrupt it and crash it before it has completed: https://jonathanlewis.wordpress.com/2014/09/08/assm-truncate/ (and https://twitter.com/kevinclosson/status/508404682432454656/photo/1 )

Regards

Jonathan Lewis