Recovering a Large Table After an Accidental Delete
SHIPDB is a 19c database with an 8 TB size, ARCHIVELOG mode, and undo retention set to 4 hours. At 09:10 a developer ran DELETE FROM SHIPMENTS without a WHERE clause and committed at 09:12; 3.2 million rows were removed. At 09:20 a batch job inserted 40,000 new shipments and updated 120,000 existing rows in the same table. At 10:40 the application team noticed the missing data. SHIPMENTS is 280 GB, has five indexes, two foreign keys referencing it, and one materialized view log. The database must stay open for other schemas, and the business wants the table restored with minimal downtime. The table is not partitioned and does not have row movement enabled. A recent level 0 backup and archived logs are available. Flashback Table to before 09:10 is being considered.
1. Assess whether Flashback Table can meet the requirement. What technical and business issues apply?
Read the worked answer
Flashback Table is risky here. It requires ROW MOVEMENT to be enabled, so there is an immediate DDL change. More importantly, it depends on undo being retained for every block changed since the target time. The delete removed 3.2 million rows, and the later batch inserted 40,000 rows and updated 120,000 rows. Reconstructing the pre-09:10 table means undoing all of that, which can generate enormous undo and easily hit ORA-01555 even with four hours of retention.
The business issue is equally important: Flashback Table rewinds the entire table to 09:10, so it discards the legitimate 40,000 inserts and 120,000 updates made after the delete. If those must be preserved, Flashback Table is the wrong tool. The table also has foreign keys and a materialized view log, which can complicate or block the operation. Flashback Query to identify deleted rows is safer than Flashback Table if the goal is to reinsert only the missing shipments, but scanning a 280 GB table and reconciling post-delete changes still needs testing.
2. Recommend an alternative and outline the recovery steps.
Read the worked answer
Use an auxiliary clone and extract the table from a point before the delete, rather than rewinding production. Restore the level 0 backup and recover the clone to 09:09, then export SHIPMENTS with Data Pump from the clone. In production, rename the damaged table to SHIPMENTS_OLD, import the clone copy under a new name, and reconcile the 40,000 new rows and 120,000 updates using primary keys and timestamps. Then rename the reconciled table into place, rebuild or re-enable indexes and constraints, and recreate the materialized view log.
If SHIPMENTS were in its own tablespace, tablespace point-in-time recovery on the auxiliary clone would be an option, but it would recover the whole tablespace, not just this table. Since the table is not partitioned, partition exchange is not available. If the deleted rows can be identified by key and undo is still available, Flashback Query into a new table is a lower-impact alternative, but the 280 GB scan and undo pressure must be tested first.
3. What would you do about dependent objects and the 40,000 rows inserted after the delete?
Read the worked answer
Treat dependent objects as part of the recovery, not an afterthought. Capture the definitions of the five indexes, two foreign keys, and the materialized view log before the swap. Foreign keys may need to be disabled during the rename/import and revalidated afterward; check for orphaned child rows. Rebuild indexes after the bulk load rather than maintaining them row by row. Recreate the materialized view log with the same columns and options, then refresh dependent materialized views.
For the 40,000 inserts and 120,000 updates, extract their keys from the current table before renaming it. After the clone copy is in place, apply those changes from SHIPMENTS_OLD using a merge or a Data Pump import with a transformation, or by comparing row timestamps. Validate row counts by date range and check the foreign keys. Keep SHIPMENTS_OLD until the application reconciliation is signed off, then drop it after a backup.