Oracle’s Recycle Bin – Part 2

Last week we explored the purpose of the recycle bin, what goes into the recycle bin and how to bypass the recycle bin.

This week we’ll explore how to get objects back from the recycle bin, after all this is the main reason why you have a recycle bin.

Restore A Table From The Recycle Bin

For all the table restores, use the FLASHBACK TABLE statement.
When restoring a table from the recycle bin, you can use either the original name of the table, or the recycle bin name of the table.

drop table hr.employees;

select object_name ,original_name
from dba_recyclebin
where original_name='EMPLOYEES'
and owner='HR';

OBJECT_NAME ORIGINAL_NAME
------------------------------ ------------------------------
BIN$e8O/g1nXdXbgUwEAAH9GYg==$0 EMPLOYEES

FLASHBACK TABLE "BIN$e8O/g1nXdXbgUwEAAH9GYg==$0" to before drop;
-- or
FLASHBACK TABLE HR.EMPLOYEES to before drop;

What happens if another HR.EMPLOYEES table already exist, and you try to restore the HR.EMPLOYEES table ?

You will receive an error:
ERROR at line 1:
ORA-38312: original name is used by an existing object

If a table with the same name already exists, and you want to restore the original table, then you must also rename the original table during the restore:

FLASHBACK TABLE "BIN$e8O/g1nXdXbgUwEAAH9GYg==$0"
to before drop rename to EMP_ORIG;

Once you restore a table from the recycle bin, that table is removed from the recycle bin, you will no longer find it there:

select object_name ,original_name
from dba_recyclebin
where original_name='EMPLOYEES'
and owner='HR';

no rows selected

If you drop a table multiple times, you will have multiple versions of the table in the recycle bin.
If you want to recover a specific version of the table, then it is recommended to use the recycle bin name of the table, and not the original name of the table in the FLASHBACK TABLE statement.
If you use the original name, then the last table that was dropped will get restored.

Restore Dependent Objects From The Recycle Bin

When you restore a table from the recycle bin, the dependent objects get also restored.
However, the dependent objects do not get renamed to their original name. Indexes, triggers, constraints, will all have their recycle bin name, after the restore.
You will have to manually rename them after restoring the table.

It is recommended you get dependent objects’ original name, prior to the restore, so you know what to rename your objects to.

select object_name, original_name, type
from dba_recyclebin
where original_name='EMPLOYEES' and owner='HR';

OBJECT_NAME                    ORIGINAL_NAME TYPE
------------------------------ ------------- ------
BIN$e8O/g1nedXbgUwEAAH9GYg==$1 EMP_IDX&      INDEX
BIN$e8O/g1nfdXbgUwEAAH9GYg==$0 EMPLOTEES     TABLE

flashback table hr.employees to before drop;

select index_name from dba_indexes
where table_name='EMPLOYEES'
and owner='HR';

INDEX_NAME
-------------------------------
BIN$e8O/g1nedXbgUwEAAH9GYg==$1

alter index hr."BIN$e8O/g1nedXbgUwEAAH9GYg==$1" rename to EMP_IDX;

Now you have a good understanding of the recycle bin, and know how to restore a table from it!

If you enjoyed this article, and would like to learn more about databases, please sign up below, and you will receive

The Ultimate 3 Step Guide To Find The Root Cause Of The Slow Running SQL!