Do DDL statements always give you an implicit commit, or can you get an implicit rollback? Do DDL statements always give you an implicit commit, or can you get an implicit rollback? oracle oracle

Do DDL statements always give you an implicit commit, or can you get an implicit rollback?


No, it will always commit.

If you want to rollback, you'll have to do it before the DDL.

If you want to isolate the DDL from your existing transaction, then you will have to execute it in its' own, separate transaction.


Technically DDL does a commit BEFORE it executes and AFTER it executes.

Yes same link from Cookie but this is a different aspect of the same issue. It's crucial to understand it's not just one commit, there are two and they happen just before and just after.


Actually it will commit IF IT CAN. If it can't successfully commit, the DDL will fail.One way to stop it committing is have a deferred constraint violated.

create table fred (id number);alter table fred add constraint id_ck check (id >0) initially deferred;insert into fred values (-1);SQL> create table junk(val number);create table junk(val number)*ERROR at line 1:ORA-02091: transaction rolled backORA-02290: check constraint (GC_REF.ID_CK) violatedSQL> desc junkERROR:ORA-04043: object junk does not exist

So if you want to prevent an implicit commit, have a dummy table with a deferred constraint. Insert a violating row in it, and you can make sure the transaction can't be committed until that violation is resolved (eg row deleted).