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

sql oracle transactions ddl
Nick Pierpoint picture Nick Pierpoint · Apr 8, 2009 · Viewed 44.8k times · Source

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

JosephStyons picture JosephStyons · Apr 8, 2009

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.

Related questions

Is there a difference between commit and rollback in a transaction only having selects?

The in-house application framework we use at my company makes it necessary to put every SQL query into transactions, even though if I know that none of the commands will make changes in the database. At the end of the …

Read More
Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More