Flashback Table, Recycle Bin, and Safe Object/Data Recovery Workflows

Learning outcomes

Two ServiceHub incidents look similar but require different recovery mechanisms. In the first, an operator commits a mass update that changes 500 rows incorrectly. In the second, someone executes DROP TABLE. Flashback Table uses retained undo to create current DML that returns a table to an earlier state. Flashback Drop instead retrieves a dropped table and associated segments from the recycle bin. Neither is a replacement for a backup when the required history/object has been purged.

1. Flashback Table rewinds data online by applying undo-derived DML

Flashback Table uses undo to recreate the table contents as of a target SCN/timestamp/restore point while the rest of the database remains online. It is a database change—not a read-only query—and cannot itself be rolled back. Oracle recommends recording the current SCN first so you can flash forward again if needed.

For TO SCN/TIMESTAMP, the table must have row movement enabled because Flashback Table can delete/reinsert rows and therefore cannot promise that old ROWIDs remain stable.

2. Build a table with dependent objects

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE servicehub_table_case PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE servicehub_table_case (  case_id NUMBER PRIMARY KEY,  status_code VARCHAR2(12) NOT NULL,  amount NUMBER(10,2) NOT NULL,  CONSTRAINT sh16_amount_ck CHECK (amount >= 0));CREATE INDEX sh16_status_ixON servicehub_table_case(status_code);CREATE OR REPLACE TRIGGER sh16_case_buBEFORE UPDATE ON servicehub_table_caseFOR EACH ROWBEGIN  :NEW.status_code := UPPER(:NEW.status_code);END;/INSERT INTO servicehub_table_case VALUES (1,'OPEN',100);INSERT INTO servicehub_table_case VALUES (2,'OPEN',200);COMMIT;VARIABLE good_scn NUMBERBEGIN :good_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER; END;/

3. Reproduce the real prerequisite failure: ORA-08189

UPDATE servicehub_table_caseSET status_code='CLOSED',    amount=amount*10;COMMIT;

FLASHBACK TABLE servicehub_table_case TO SCN :good_scn;-- Expected:-- ORA-08189: cannot flashback the table because row movement is not enabled

Oracle may change ROWIDs while deleting/reinserting the historical row set. Row movement is therefore an explicit acknowledgment that applications must not treat ROWID as permanent business identity.

4. Repair the prerequisite and flash the table back

ALTER TABLE servicehub_table_case ENABLE ROW MOVEMENT;VARIABLE before_flashback_scn NUMBERBEGIN :before_flashback_scn := DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER; END;/FLASHBACK TABLE servicehub_table_case TO SCN :good_scn;SELECT case_id,status_code,amountFROM servicehub_table_caseORDER BY case_id;-- Expected: OPEN / 100 and OPEN / 200

By default Oracle disables enabled triggers on the affected table during the Flashback Table operation, then restores each trigger's previous enabled/disabled state. Add ENABLE TRIGGERS only when you explicitly need triggers to fire during the rewind and have tested the consequences.

5. Verify dependencies after Flashback Table

SELECT constraint_name,constraint_type,status,validatedFROM user_constraintsWHERE table_name='SERVICEHUB_TABLE_CASE'ORDER BY constraint_name;SELECT index_name,status,uniquenessFROM user_indexesWHERE table_name='SERVICEHUB_TABLE_CASE'ORDER BY index_name;SELECT trigger_name,statusFROM user_triggersWHERE table_name='SERVICEHUB_TABLE_CASE';SELECT row_movementFROM user_tablesWHERE table_name='SERVICEHUB_TABLE_CASE';

Flashback Table preserves current table attributes needed by the application, including indexes/triggers/constraints, but dependent-table referential integrity can still block a flashback if the requested historical state would violate current constraints. Flash related tables together when needed.

6. Flashback Drop uses the recycle bin, not ordinary undo history

DROP TABLE without PURGE normally renames the table/associated eligible objects to system-generated BIN$... names and retains their segments in the recycle bin. This is why Flashback Drop can restore a table even though the original SQL name no longer exists.

DROP TABLE servicehub_table_case;SELECT  object_name,  original_name,  operation,  type,  droptime,  can_undrop,  can_purgeFROM recyclebinWHERE original_name IN (  'SERVICEHUB_TABLE_CASE',  'SH16_STATUS_IX',  'SH16_CASE_BU')ORDER BY droptime,object_name;

7. Restore the dropped table safely

FLASHBACK TABLE servicehub_table_case TO BEFORE DROP;SELECT case_id,status_code,amountFROM servicehub_table_caseORDER BY case_id;SELECT constraint_name,statusFROM user_constraintsWHERE table_name='SERVICEHUB_TABLE_CASE';SELECT index_name,statusFROM user_indexesWHERE table_name='SERVICEHUB_TABLE_CASE';SELECT trigger_name,statusFROM user_triggersWHERE table_name='SERVICEHUB_TABLE_CASE';

The table and recoverable dependent objects return, but names of recovered indexes/constraints can require attention when system-generated recycle-bin names are involved or when name conflicts were created after the drop. Foreign-key relationships are not simply “rewound as a group”; verify them explicitly.

8. Multiple drops with the same original name need deterministic selection

If SERVICEHUB_TABLE_CASE was created/dropped several times, the recycle bin can contain multiple generations. FLASHBACK TABLE original_name TO BEFORE DROP selects the most recently dropped object with that original name. For an older generation, use its unique BIN$... name and optionally RENAME TO a safe recovery name.

-- Example only: copy the exact OBJECT_NAME from RECYCLEBIN.FLASHBACK TABLE "BIN$unique_generated_name==$0"  TO BEFORE DROP  RENAME TO servicehub_table_case_recovered;

9. Deliberately wrong: DROP TABLE ... PURGE and assume recycle-bin recovery remains

PURGE bypasses/removes the recycle-bin safety net. Space pressure can also cause Oracle to purge old recycle-bin objects automatically, and some table types/policies are not protected by Flashback Drop. A successful DROP TABLE therefore does not guarantee future undrop capability.

10. Cleanup

DROP TABLE servicehub_table_case PURGE;

11. Production judgment

Use Flashback Table for bounded logical mistakes when undo reaches the target and dependent tables can remain consistent. Use Flashback Drop immediately for eligible accidental drops and verify restored object names, indexes, constraints, triggers, grants, and application behavior. Keep ROWID out of durable business identity designs.

Flashback Table is available in current Free. No management pack or restart is required, but the Flashback Table privilege/DML/ALTER permissions and row movement prerequisites matter. Lesson 4 moves from one table to a physical CDB/PDB rewind using flashback logs—an operation with much larger storage and blast-radius consequences.

Authoritative references

• Performing Flashback and Database PITR — Flashback Table and Flashback Drop prerequisites/workflows
• FLASHBACK TABLE — table rewind/drop syntax and trigger behavior
• Managing Tables — recycle-bin object behavior
• ORA-08189 — row-movement prerequisite failure
• Licensing Information — Flashback Table availability by offering