Hi All, is it possible to have only DDL statements with a transaction? and if so, is it possible to get it rollback to the original state, if any one of the DDL statement fails?
Thanks & Regards, priya.R
+6
A:
Hi Priya,
in Oracle a DDL statement implicitely commits before and after the statement. Each statement is an independant transaction and you won't be able to rollback a successful statement.
A DDL statement however is always atomic: it either succeeds entirely or fails entirely.
Vincent Malgrat
2009-10-26 10:41:11
Why before? Its not needed.
Adeel Ansari
2009-10-26 10:43:06
@Vinegar: I'm not sure why Oracle commits before a DDL. Probably to release DML Locks and acquire DDL locks on the objects.
Vincent Malgrat
2009-10-26 10:46:35
Yeah, may be. It makes sense.
Adeel Ansari
2009-10-26 10:55:56