0

I am new to Oracle Advance concepts, In my application I have accidentally "truncate" the data. I am facing difficulty to restore the truncated data. I googled about it alot, I found there is "FLASHBACK" command from oracle 10g. I tried it using timestamp approach in flashback but I am getting the following error:

Error: The table definition has changed.

Could any body tell me how to get back my truncated table data?

Is it possible? If it is Please let me know the procedure to get back the data.

M.S.Naidu
  • 2,239
  • 5
  • 32
  • 56

1 Answers1

3

The error message is pretty clear. FLASHBACK is not available if you had DDL operations on the table (add or drop column, add or drop constraint, etc.) It's right there in the first paragraph in the documentation. http://docs.oracle.com/cd/B28359_01/server.111/b28286/statements_9012.htm As Tim suggests - this is where backing up your data would have helped.

Edit: To be very precise, the wording in the Oracle documentation is (with my emphasis): Oracle Database cannot restore a table to an earlier state across any DDL operations that change the structure of the table. So adding or dropping a column will prevent the use of FLASHBACK since they change the structure of the table. TRUNCATE does not, so TRUNCATE by itself does not prevent FLASHBACK (although TRUNCATE is a DDL operation). I got carried away in the first paragraph with "add or drop constraint" - I actually don't know if that will prevent FLASHBACK; one can find out by experimenting.

  • Hello @mathguy, If I understood the document properly what ever you have shared. "TO BEFORE DROP Clause" can be done using FLASHBACK but it is also a DDL command. Can you please let me know if I am wrong? – M.S.Naidu Oct 24 '16 at 13:33
  • @M.S.Naidu - I clarified my answer further - see above. –  Oct 24 '16 at 13:45