Understanding Flashback Technology - OCP: Oracle Database 12c Administrator Certified Professional - page 366

And, as you can probably guess, the Recycle Bin feature can be reenabled by all the same
methods; just substitute ON for OFF .
The ALTER SYSTEM ... DEFERRED statement applies the global value to
future sessions that connect to the database but does not modify the value
for existing sessions.
Restoring Objects with Flashback Drop
Now that you've seen how the Recycle Bin works, it's time to use the Flashback Drop feature
to restore objects from it. The syntax for the Flashback Drop command is as follows:
FLASHBACK TABLE table_name
TO BEFORE DROP
[RENAME TO new_table_name];
By default, a Flashback Table operation will restore the table using the same name it had
originally. The optional RENAME clause allows you to restore the table under a different name.
This may be required if an object with the original name already exists in your schema.
Let's take a look at how it all works. In this example, we will restore the JOB_HISTORY
table and rename it JOB_HIST :
SQL> flashback table job_history to before drop rename to job_hist;
Flashback complete.
Now we'll query the restored table, as shown here:
SQL> select * from job_hist;
JOB_ID 1
As you can see, the Flashback Drop feature was successful in restoring the table. However,
we had two different versions of the JOB_HISTORY table in the Recycle Bin. Why did Oracle
choose to restore this one instead of the other one? The answer is that Oracle will always
choose to restore the most recently dropped version of the table (if it has two identically
named tables).
To recover the previous version of the JOB_HISTORY table, we have two options. Since we
already recovered the first one, we can simply execute the Flashback Drop command again
to restore the second version (however, we must rename one of them). However, a better
alternative is to use the system-assigned object name whenever you want to recover a specific
version of a table. Here's an example:
SQL> flashback table "BIN$NIaEC81/SJ24Dqr+fTvqTQ==$0" to before drop;
Flashback complete.