ADVERTISEMENT

How to Recover Truncated Table Using Flashback in Oracle

3 min read

Truncating a table removes all data instantly, and a simple rollback cannot undo it. But if Flashback Database was enabled before the truncate occurred, you can restore the table to an earlier SCN or timestamp.
This guide includes how to check Flashback status, how to get the SCN, how to flashback, and all post-steps—everything in one place.

1. Check if Flashback Is Enabled

SELECT flashback_on FROM v$database;

If the result is YES, flashback recovery is possible.
Note: During flashback, triggers remain disabled automatically.

2. How to Find the Correct SCN

You can restore the table to an SCN just before the truncate happened.
Here are the main ways to get the SCN.

a. Using Alert Log (shows TRUNCATE time → convert to SCN)

Find the timestamp of TRUNCATE in alert log, then get SCN from that timestamp:

SELECT TIMESTAMP_TO_SCN(TO_TIMESTAMP('2024-04-15 19:00:00',
       'YYYY-MM-DD HH24:MI:SS')) AS scn_value
FROM dual;

b. Get current SCN at any time

Useful if you know the truncate happened shortly before:

SELECT current_scn FROM v$database;

c. Use Flashback Query to inspect history

If table history exists:

SELECT versions_startscn, versions_endscn
FROM TEST VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;

This gives you the SCN range where data existed before truncate.

d. Convert timestamp to SCN directly

If you know the exact time when truncate happened:

SELECT TIMESTAMP_TO_SCN(TO_TIMESTAMP('2024-04-15 19:00:00',
       'YYYY-MM-DD HH24:MI:SS')) AS scn_value
FROM dual;

e. Convert SCN to timestamp (reverse check)

SELECT SCN_TO_TIMESTAMP(<scn_number>) FROM dual;

3. Flashback Steps

a. Enable Row Movement

ALTER TABLE TEST ENABLE ROW MOVEMENT;

b. Flashback the table using SCN

FLASHBACK TABLE TEST TO SCN <scn_number>;

or flashback using timestamp

FLASHBACK TABLE TEST TO TIMESTAMP TO_TIMESTAMP(
       '2024-04-15 19:00:00',
       'YYYY-MM-DD HH24:MI:SS');

4. Post-Recovery Steps

Verify recovered rows

SELECT COUNT(*) FROM TEST;

Disable row movement again

ALTER TABLE TEST DISABLE ROW MOVEMENT;

Check and recompile invalid dependent objects

Check invalid:

SELECT object_name, status
FROM user_objects
WHERE object_type IN ('VIEW','TRIGGER')
AND status <> 'VALID';

Recompile:

ALTER VIEW my_view COMPILE;
ALTER TRIGGER my_trigger COMPILE;
ALTER PROCEDURE my_procedure COMPILE;
ALTER FUNCTION my_function COMPILE;

5. Summary

To recover a truncated table using Flashback in Oracle:

  1. Ensure Flashback is enabled.
  2. Get the SCN (using alert log time, flashback query, timestamp conversion, or current SCN).
  3. Enable row movement.
  4. Flashback to SCN or timestamp.
  5. Verify data and recompile objects.
Close ✖