Menu
Could in Oracle DDL (create/alter) be transactional like they are in MS SQL (started from 2005)?
475
When you execute a DDL operation, there is an implied commit after the DDL. The save point is then changed to the position following the DDL operation. Therefore, any rollbacks will only undo changes to the new save point - which is after the last DDL operation.
However, the second DELETE statement has been rolled back, because it is issued after the DDL and before the ROLLBACK command. So, now it becomes obvious that DDL commands in Oracle cannot be rolled back and included in one transaction.
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.
Oracle DatabaseOracle does not support transactional DDL: the transaction is considered as closed when CREATE, DROP, RENAME or ALTER command is executed. If the transaction contains DML commands, the Oracle commits the transaction as a whole and then commits the DDL command as a separate transaction.
DDL is not transactional in Oracle. From the 11.2 doc:
Oracle Database implicitly commits the current transaction before and after every DDL statement.
179
No. In Oracle DDL statements themselves are not transactional.
Running a DDL statement will implicitly commit any open transaction for that session before starting the actual work.
In addition some statements, like an alter table statement, may fail if another session has an open transaction on the object being modified or one of its dependencies. You can set a ddl_lock_timeout to specify how long you want Oracle to wait for the object to become available.
See DDL Statements for a summary of types of DDL statements and information about their behaviour regarding locks and transactions.
Oracle Database implicitly commits the current transaction before and after every DDL statement.
37
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
