1

I have searched for the flashback command. Somewhere it is written we can rollback the truncate operation, and somewhere it is written we can't rollback. Can the FLASHBACK command in Oracle used to rollback the truncate table operation?

Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
Yogesh Mittal
  • 187
  • 2
  • 12
  • Flashback isn't the same as rollback, but no, it can't recover from a truncate and you can't do a flashback query past a truncate. It can recover from a `drop table` which might be the confusion. Perhaps you could quote and/or link to the sources for the two conflicting statements? – Alex Poole Sep 20 '14 at 15:57

1 Answers1

3

You need to have Flashback Data Archive enabled. Then the truncate could be reversed with a statement like this: 

insert into <truncated_table> 
   select * from <truncated_table> as of timestamp <some time>;

Thanks to @Jon Heller for adding this useful information about the new feature:

This requires version 11.2 or higher, which introduced support for DDL statements such as truncate.

Community
  • 1
  • 1
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
  • 1
    You still [can't recover from a truncate](http://docs.oracle.com/cd/B28359_01/appdev.111/b28424/adfns_flashback.htm#BJFJHDAG), though? Or rather, can't truncate if this is enabled; but either way you can't 'rollback' a truncate? – Alex Poole Sep 20 '14 at 21:55
  • My intention was to focus on the other way round. I am not saying it would rollback the truncated data. But ***it will let the archived historical data to be accessed with standard SQL statements*** – Lalit Kumar B Sep 21 '14 at 07:38
  • @Jon, thank you for the edit. Makes much more sense now. – Lalit Kumar B Sep 22 '14 at 07:43
  • Ah, I didn't realise truncate (or the other DDL in JonHeller's link) is allowed from 11gR2. Interesting. – Alex Poole Sep 22 '14 at 14:12
  • And just to add that this feature is not available Express Edition. http://www.oracle.com/us/products/database/enterprise-edition/comparisons/index.html – David Aldridge Jun 30 '15 at 08:30
  • @DavidAldridge Yes, saw your updated post here http://stackoverflow.com/a/139633/3989608 – Lalit Kumar B Jul 03 '15 at 11:10