{"id":5006,"date":"2026-01-15T12:46:51","date_gmt":"2026-01-15T07:16:51","guid":{"rendered":"https:\/\/w3buddy.com\/?p=5006"},"modified":"2026-01-20T10:13:58","modified_gmt":"2026-01-20T04:43:58","slug":"how-to-recover-truncated-table-using-flashback-in-oracle","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/how-to-recover-truncated-table-using-flashback-in-oracle\/","title":{"rendered":"How to Recover Truncated Table Using Flashback in Oracle"},"content":{"rendered":"\n

Truncating a table removes all data instantly, and a simple rollback cannot undo it. But if Flashback Database<\/strong> 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<\/strong>\u2014everything in one place.<\/p>\n\n\n\n

1. Check if Flashback Is Enabled<\/h2>\n\n\n\n
SELECT flashback_on FROM v$database;<\/code><\/pre>\n\n\n\n
If the result is YES<\/strong>, flashback recovery is possible.
Note: During flashback, triggers remain disabled automatically.<\/p>\n\n\n\n
2. How to Find the Correct SCN<\/h2>\n\n\n\n
You can restore the table to an SCN just before the truncate happened.
Here are the main ways to get the SCN.<\/p>\n\n\n\n
a. Using Alert Log (shows TRUNCATE time \u2192 convert to SCN)<\/h3>\n\n\n\n
Find the timestamp of TRUNCATE in alert log, then get SCN from that timestamp:<\/p>\n\n\n\n
SELECT TIMESTAMP_TO_SCN(TO_TIMESTAMP('2024-04-15 19:00:00',\n       'YYYY-MM-DD HH24:MI:SS')) AS scn_value\nFROM dual;<\/code><\/pre>\n\n\n\n
b. Get current SCN at any time<\/h3>\n\n\n\n
Useful if you know the truncate happened shortly before:<\/p>\n\n\n\n
SELECT current_scn FROM v$database;<\/code><\/pre>\n\n\n\n
c. Use Flashback Query to inspect history<\/h3>\n\n\n\n
If table history exists:<\/p>\n\n\n\n
SELECT versions_startscn, versions_endscn\nFROM TEST VERSIONS BETWEEN SCN MINVALUE AND MAXVALUE;<\/code><\/pre>\n\n\n\n
This gives you the SCN range where data existed before truncate.<\/p>\n\n\n\n
d. Convert timestamp to SCN directly<\/h3>\n\n\n\n
If you know the exact time when truncate happened:<\/p>\n\n\n\n
SELECT TIMESTAMP_TO_SCN(TO_TIMESTAMP('2024-04-15 19:00:00',\n       'YYYY-MM-DD HH24:MI:SS')) AS scn_value\nFROM dual;<\/code><\/pre>\n\n\n\n
e. Convert SCN to timestamp (reverse check)<\/h3>\n\n\n\n
SELECT SCN_TO_TIMESTAMP(<scn_number>) FROM dual;<\/code><\/pre>\n\n\n\n
3. Flashback Steps<\/h2>\n\n\n\n
a. Enable Row Movement<\/h3>\n\n\n\n
ALTER TABLE TEST ENABLE ROW MOVEMENT;<\/code><\/pre>\n\n\n\n
b. Flashback the table using SCN<\/h3>\n\n\n\n
FLASHBACK TABLE TEST TO SCN <scn_number>;<\/code><\/pre>\n\n\n\n
or flashback using timestamp<\/h3>\n\n\n\n
FLASHBACK TABLE TEST TO TIMESTAMP TO_TIMESTAMP(\n       '2024-04-15 19:00:00',\n       'YYYY-MM-DD HH24:MI:SS');<\/code><\/pre>\n\n\n\n
4. Post-Recovery Steps<\/h2>\n\n\n\n
Verify recovered rows<\/h3>\n\n\n\n
SELECT COUNT(*) FROM TEST;<\/code><\/pre>\n\n\n\n
Disable row movement again<\/h3>\n\n\n\n
ALTER TABLE TEST DISABLE ROW MOVEMENT;<\/code><\/pre>\n\n\n\n
Check and recompile invalid dependent objects<\/h3>\n\n\n\n
Check invalid:<\/p>\n\n\n\n
SELECT object_name, status\nFROM user_objects\nWHERE object_type IN ('VIEW','TRIGGER')\nAND status <> 'VALID';<\/code><\/pre>\n\n\n\n
Recompile:<\/p>\n\n\n\n
ALTER VIEW my_view COMPILE;\nALTER TRIGGER my_trigger COMPILE;\nALTER PROCEDURE my_procedure COMPILE;\nALTER FUNCTION my_function COMPILE;<\/code><\/pre>\n\n\n\n
5. Summary<\/h2>\n\n\n\n
To recover a truncated table using Flashback in Oracle:<\/p>\n\n\n\n
    \n
  1. Ensure Flashback is enabled.<\/li>\n\n\n\n
  2. Get the SCN (using alert log time, flashback query, timestamp conversion, or current SCN).<\/li>\n\n\n\n
  3. Enable row movement.<\/li>\n\n\n\n
  4. Flashback to SCN or timestamp.<\/li>\n\n\n\n
  5. Verify data and recompile objects.<\/li>\n<\/ol>\n","protected":false},"excerpt":{"rendered":"
    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\u2014everything in one […]<\/p>\n","protected":false},"author":1,"featured_media":5008,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-5006","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5006","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=5006"}],"version-history":[{"count":1,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5006\/revisions"}],"predecessor-version":[{"id":5007,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5006\/revisions\/5007"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media\/5008"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=5006"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=5006"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=5006"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}