As DDL implicitly commits, the only way to "rollback" your changes is to construct the reverse operation and apply it to revert the change, as a_horse_with_no_name states.
Constructing such a rollback won't always be straightforward however. If data could be written to the table between type modifications (varchar2(10) -> varchar2(50), number -> varchar2) and rolling this back then you'll also have to check the new data will be valid when reverting to the original type (or perform some conversion). Be aware that dropping columns on large tables could take some time and generate large amounts of redo.
You'll also have to be wary of invalidating any stored procedures on your database and other application dependencies as a result of these changes.
The flashback option won't help you in this instance. Once you've made DDL changes to a table, you can't restore it to it's previous state using flashback. Trying to do so will give you the error:
ORA-01466: unable to read data - table definition has changed
Flashing-back your full database would be overkill and also not possible from the Java app - you need to shut down and then mount the database to complete this operation.
Which all raises the question of what your tool is for. If you just need a GUI for people to edit tables, then something like Oracle SQL Data Modeler can do this and generate DDL scripts for you. These can then be validated, tested, an appropriate rollback constructed and applied to the database. Modifying the structure of a (production) database should be done with care and tested to ensure that all changes are valid!
I believe it will mean the same thing for DDL as it does for DML. The msdn article on the topic actually gives you a pretty clear idea under the SERIALIZABLE
section:
This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.
Basically as long as your transaction is running, no DDL can be performed on any of the objects you directly or indirectly reference.
Best Answer
You can issue multiple DDL statements in a single transaction using the
CREATE SCHEMA
command though you are limited to just theCREATE TABLE
,CREATE VIEW
, andGRANT
statements.If you are trying to upgrade an existing database, depending on the Oracle edition and configuration, I would tend to suspect that you'd be better served with something like Oracle Flashback Database. If you create a guaranteed restore point before you start the upgrade, for example, you can simply flashback the database to that restore point if the upgrade fails regardless of how many transactions you've committed during the upgrade.