I dropped a table in Oracle, how can I retrieve it from the undo tablespace?

sql oracle undo flashback sql-drop
user2836455 picture user2836455 · Oct 1, 2013 · Viewed 8.1k times · Source

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.

Answer

Przemyslaw Kruglej picture Przemyslaw Kruglej · Oct 1, 2013

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.

Related questions

Get list of all tables in Oracle?

How do I query an Oracle database to display the names of all tables in it?

Read More
How do I limit the number of rows returned by an Oracle query after ordering?

Is there a way to make an Oracle query behave like it contains a MySQL limit clause? In MySQL, I can do this: select * from sometable order by name limit 20,10 to get the 21st to the 30th rows (skip the …

Read More
Oracle SQL: Update a table with data from another table

Table 1: id name desc ----------------------- 1 a abc 2 b def 3 c adf Table 2: id name desc ----------------------- 1 x 123 2 y 345 In oracle SQL, how do I run an sql update query that can update Table 1 with Table 2's name and desc using …

Read More