6
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?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
glenneroo
  • 1,908
  • 5
  • 30
  • 49

2 Answers2

6

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.

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • Via your links (and Google), i'm only able to find a way to restore single tables. Do you know of a way to flashback the entire restore point? – glenneroo Aug 19 '11 at 13:50
  • 1
    @glenneroo - You can flashback an entire database to a restore point http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_9011.htm and http://www.orafaq.com/node/1847 are good primers. – Justin Cave Aug 19 '11 at 13:52
  • I guess it's not available for Express Edition. Executing the command **alter database flashback on;** returns the following error: *ORA-00439: feature not enabled: Flashback Database*. Alternately, to check if the feature is available, just run **select flashback_on from v$database;**. – glenneroo Aug 23 '11 at 11:13
3

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?

Community
  • 1
  • 1
Lost in Alabama
  • 1,653
  • 10
  • 16