Database Tutorials MSSQL, Oracle, PostgreSQL, MySQL, MariaDB, DB2, Sybase, Teradata, Big Data, NOSQL, MongoDB, Couchbase, Cassandra, Windows, Linux 
In today’s article, we will explain how we can recover a dropped table with Oracle Flashback.
If a table with certain constraints is dropped and then tried to be brought back 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.
However, if it has not been deleted with PURGE, it can be restored as follows.
1. The table is dropped.
1 2 3 | SQL> drop table oardahanli.test; Table dropped. |
2. The table is queried and it is confirmed that it does not exist.
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. The table is recovered 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 |
If there are two 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.
