Restore a table with flashback database even if I used “drop table purge”

flashbackoracle-12c

If I issue the following statement:
drop table DontDrop purge;

It's going to be flushed out of my recycle bin so flashback statement won't work:

select * from DontDrop as of timestamp to_timestamp(sysdate-1);

Now, can I use a flashback database statement to restore it?

flashback database to timestamp (sysdate-1);

Best Answer

Flashback database can be used to restore a dropped table that is no longer in the recyclebin.

Provided the following is true:

1) Flashback database is turned on which you can check via:

select flashback_on from v$database;

2) You have the flashback logs to flashback that far, which you can check via:

select oldest_flashback_time from v$flashback_database_log;.

3) You have the backup archived redo/archived redo on disk to flashback the database that far.

The syntax looks like the following:

sqlplus / as sysdba
shutdown immediate;
startup mount;
FLASHBACK DATABASE TO TIME 'SYSDATE-1';
startup open resetlogs;

The Oracle Database 12c docs about flashback database can be found here.