Database Reference
In-Depth Information
The time was 16:12:35 when something bad (exemplified next) happened:
16:12:35 USER1@PDB1> delete from fbtab1 where object_id < 10000;
9645rows deleted.
USER1@PDB1> commit;
Commit complete.
Almost 10,000 rows have been deleted by accident, causing logical corruption. Luckily the user picked up the phone
to the DBA on duty and reported his problem. Using the time and the useful
timetamp_to_scn
function the DBA could
use the flashback table command to get the table back to what it was before the accident, and no damage was done.
DBA1@PDB1> flashback table user1.fbtab1 to scn 983544;
Flashback complete.
DBA1@PDB1> select count(1) from user1.fbtab1 where object_id < 10000;
COUNT(1)
----------
9645
DBA1@PDB1> select count(1) from fbtab1;
COUNT(1)
----------
89646
Flashback table can be extended to cater to dropped objects as well. Assume for a moment that instead of a
delete the user issued a drop table command:
USER1@PDB1> drop table fbtab1;
Table dropped.
The user might be lucky if the 10g “recycle bin” feature was active at the time. If so, just like on your desktop
operating system, you can move things out of the recycle bin and back into active service:
USER1@PDB1> select object_name,original_name,type from user_recyclebin;
OBJECT_NAME ORIGINAL_NAME TYPE
------------------------------ ------------------------------ -------------------------
BIN$yZfeIpNmMcHgQwqYqMAW7Q==$0 FBTAB1 TABLE
You or your DBA on duty can now restore the table to before the drop using a variation of the flashback
table command:
DBA1@PDB1> flashback table user1.FBTAB1 to before drop;
Flashback complete.
Search WWH ::
Custom Search