0

I accidentally dropped a fairly large table -- recycling bin is not enabled. I'm fairly certain the data still exists in the UNDO tablespace, but I'm not sure how to get it out. I recreated the table exactly as it was before it was dropped -- the structure is exactly the same. However, when I attempt to flashback the table, I get this error:

flashback table tablex to timestamp (systimestamp - interval '120' minute);
Error: 01466
DBD::Oracle::db do failed: ORA-01466: unable to read data - table definition has changed

Any idea how I can overcome this error? From all of the searching I've done, it seems as if it believes the table is not structurally the same as when it was dropped.

Midhun MP
  • 103,496
  • 31
  • 153
  • 200
user2836455
  • 1
  • 1
  • 1
  • Hm, I didn't rollback because I thought dropping a table couldn't actually be undone by a rollback. I thought only DML operations like insert and update could be rolled back? – user2836455 Oct 01 '13 at 20:43
  • FLASHBACK TABLE doesn't require the recyling bin when using the flashback to timestamp functionality. If you're using that, it checks in the UNDO tablespace, as far as I know (I could be wrong here). – user2836455 Oct 01 '13 at 20:45
  • If recycle bin is not enabled, and you confirmed it by seeing `off` in the `value` column in the result of `show parameter recyclebin`, then really no easy way to bring the table back without to do a point-in-time recovery of database or a talespace. You also could try to restore and recover dropped table by using `LogMiner` but it probably would take much more time. – Nick Krasnov Oct 01 '13 at 21:17

1 Answers1

2

I think you should not recreate the table by yourself - it will be recreated for you by Oracle when you Flashback it, I recommend the following article: http://www.oracle-base.com/articles/10g/flashback-10g.php#flashback_table

Example:

CREATE TABLE flashback_table_test (
  id NUMBER CONSTRAINT pk_flashback_table_test PRIMARY KEY
);

DROP TABLE flashback_table_test;
FLASHBACK TABLE flashback_table_test TO BEFORE DROP;

I think you will need to flash it back using some SCN number prior to the number when you manually recreated the table.

Przemyslaw Kruglej
  • 8,003
  • 2
  • 26
  • 41