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

Question

If you're halfway through a transaction and perform a DDL statement, such as truncating a table, then the transaction commits.

I was wondering whether this was always the case and by definition, or is there a setting hidden somewhere that would rollback the transaction instead of committing.

Thanks.

Edit to clarify...

I'm not looking to rollback after a truncate. I just want to confirm that statements already carried out are absolutely always going to be committed before a DDL. Just want to make sure there isn't a system property somewhere that someone could set to wreck my code.

I understand the need to commit before and after a DDL, but conceptually I'd have thought the same consistency requirement could be achieved with a rollback before the DDL and a commit after.

Answer 1

A truncate table or an alter table or a create table do always cause a commit.

Why do you want to rollback when you do a truncate table?

Answer 2

Here is an AskTom article that may help. From the article:

"I was wondering why DDL statements are not executed inside an autonomous transaction (like sequences do), so they wouldn´t affect any pending user transaction...

Can you clarify?

Followup June 24, 2003 - 7am US/Eastern:

that would be as "confusing" as not doing it that way. anyway, you have atrans so if you want to, you can. "

So, if you really need to, you can stick your DDL inside an Autonomous Transaction and do what you want.

EDIT: Bottom line is that unless you go to explicit lengths to "subvert" Oracle, DDL is going to perform a commit. That said, if you absolutely require that a commit is performed at a certain point, why not just perform it explicitly?

Answer 3

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.

Answer 4

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.

Answer 5

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 back
ORA-02290: check constraint (GC_REF.ID_CK) violated
SQL> desc junk
ERROR:
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).