In today’s article, we will be learning how to Recover a Dropped Table Using Oracle Flashback Drop.
If a table with certain constraints is dropped and then tried to recover with a flashback, all constraints except the Foreign Key come back, but the foreign key does not.
If a table is dropped with PURGE, it cannot be restored using the Flashback feature.
The reason is that UNDO does not occur during the DROP process and we prevent the data from being thrown into the trash bin where we can return it.
This table can only be restored from backup.
In order to restore a table with flashback, the RECYCLEBIN parameter must be ON.
|
1 2 3 4 5 |
[Primary-1] SQL> show parameter recyclebin NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ recyclebin string on |
However, if it has not been deleted with PURGE, it can be restored as follows.
If a tablespace is dropped with DROP TABLESPACE … INCLUDING CONTENTS, its objects do not go to the trash.
If there are objects in the trash can of the tablespace, they are also deleted from the trash can.
If a user is deleted with CASCADE (DROP USER … CASCADE), its objects do not go to the trash.
If there are objects in the user’s trash can, they are also deleted from the trash can.
Below is everything that can be done with Flashback.
1. We drop the table.
|
1 2 3 |
SQL> drop table oardahanli.test; Table dropped. |
2. The table is queried and it is confirmed that there is no information.
|
1 2 3 4 5 |
SQL> select count(*) from oardahanli.test; select count(*) from oardahanli.test * ERROR at line 1: ORA-00942: table or view does not exist |
3. By querying the trash can, it is seen that the dropped table is there.
|
1 2 3 4 5 |
SQL> select owner, original_name, object_name as recycle_name, type from dba_recyclebin; OWNER ORIGINAL_NAME RECYCLE_NAME TYPE ------------------------------ -------------------------------- ------------------------------ ------------------------- OARDAHANLI TEST BIN$LHAuB37JMSLgUxUqFKw85A==$0 TABLE |
4. We recover the table with the flashback table.
|
1 2 3 |
SQL> flashback table oardahanli.test to before drop; Flashback complete. |
5. The table is queried and the data is seen.
|
1 2 3 4 5 |
SQL> select count(*) from oardahanli.test; COUNT(*) ---------- 8469 |
NOTE: If there are 2 paintings with the same name in the trash can, the one that was last thrown into the trash bin is returned.
If we want to bring the old one, object_name is written in the table name.
6. Bringing the old DROP from the tables with the same name.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
[Primary-1] SQL> select object_name, original_name, operation, type, droptime, dropscn from dba_recyclebin; OBJECT_NAME ORIGINAL_NAME OPERATION TYPE DROPTIME DROPSCN ------------------------------ -------------------------------- --------- ------------------------- ------------------- ---------- BIN$bO9c0NtIM+vgUxUqFKytpw==$0 EMP DROP TABLE 2018-05-24:10:39:00 20382098 BIN$bO9c0NtNM+vgUxUqFKytpw==$0 EMP DROP TABLE 2018-05-24:10:48:10 20383383 BIN$bO9c0Ns5M+vgUxUqFKytpw==$0 EMP DROP TABLE 2018-05-24:10:11:38 20378305 [Primary-1] SQL> flashback table hr."BIN$bO9c0Ns5M+vgUxUqFKytpw==$0" to before drop; Flashback complete. [Primary-1] SQL> select count(*) from hr.emp; COUNT(*) ---------- 107 |
|
1 |
https://matthiashoys.wordpress.com/2013/05/03/oracle-11g-flashback-examples/ |
