How to undo ALTER TABLE using sqlplus (Oracle 10g Express)?

Question

rollback;

doesn't seem to undo alter table changes.

Background:
I'm generating some .sql scripts (based on parsed Hibernate scripts) which are trashing my tables. Importing the full database for testing takes up to 30 minutes (also slowing my machine) and as much as I enjoy taking breaks, i'd prefer to just undo everything with a command such as rollback and try again.

btw this is Oracle 10g Express Edition Release 10.2.0.1.0

Is this even possible?

Answer 1

With the express edition, I'm not sure this is possible. You cannot rollback a DDL operation like ALTER TABLE because DDL is implicitly committed.

Oracle does have the option to create restore points that you can then flashback the entire database to a point in time relatively quickly. That will undo the effects of all committed transactions (DML and DDL) between the creation of the restore point and the point where you issued the flashback command. Here is an example of creating and flashing back to a restore point and here's another that does the flashback for the entire database. I'm just not sure that this functionality is available in the express edition.

Answer 2

This version of Oracle performs a commit on any ALTER TABLE statements.

See this post: it possible to roll back CREATE TABLE and ALTER TABLE statements in major SQL databases?