How to Recover Truncated Tables in Oracle

  • by
Truncate Table can destroy all data of a table without footprints, only metadata left there. If you are responsible for data manipulation or migration, truncating table could be a very routine operation to you.

You could truncate many tables too often to care the risk, and too convenient to keep alert. One day, you might mistakenly truncate a source table rather than a target table.

If your’re in the worst case, here are some rescue plans for your reference. I hope some of these approaches useful:
  1. Data Pump
  2. You might have several copies comes from daily export job. It’s a good news, you can pump the data back to the empty table. As you expected, the data might be an old copy at the state of last night. Don’t be disappointed, this could be the lowest price you must pay.

    You may refer to my post about data pump as an example:
    How to Self-Import by Data Pump in Network Mode

  3. Materialized View
  4. There could be a materialized view that replicate the master table completely and periodically, say every 10 minutes, you can copy (e.g. CTAS) the data back to the table to recover it before next snapshot. Yes, it’s nice to have MV, but you must be quick.

  5. Flashback technologies
  6. Flashback table or flashback transaction does not help in this case, only flashback database works, which means you may lose some data on the other tables instead of this table. This is a trade-off.

    But the fist question is: Did you have ever enable Flashback Database before the mistake happened? I hope the answer is yes. You may refer to my post for more details about enabling Flashback Database:
    How to Enable Flashback Database

    Furthermore, the target tablespace must have never been excluded from Flashback Database. You may check the paragraph:
    How to Check Tablespace Flashback Status

  7. Standby database
  8. There could still have all data of this table in the standby database at this moment. But you must be quick: please connect to the standby database as sysdba, cancel recovery manage standby database, and try to copy all data back to the primary database.

    Chances are, the standby database might have applied the newest redo log transported from the primary database. So the key point is: if you decide to retrieve the data back from the standby database, you must take the actions immediately.

  9. RMAN
  10. Tables are only segments, it may scatteringly reside in tablespaces. So there is no such “restore table” in RMAN, but at least, you can have “restore tablespace” or perform a tablespace point-in-time recovery (TSPITR). Like other rescue plans, there’s always a trade-off between recovering old data and losing new data.

Leave a Reply

Your email address will not be published. Required fields are marked *