Recycle Bin

Recycle Bin

What is Recycle Bin?
It is a feature in recent Oracle database versions that is used to enable the Flashback Drop feature.Recyclebin is an initialization parameter in the initSID.ora file.By default Recyclebin feature is enabled.

How can you enable and disable recyclebin?
We can enable and disable the recyclebin feature using the following commands:ALTER SESSION,ALTER SYSTEM.

How do you enable Recyclebin feature?
ALTER SESSION SET recyclebin = ON;
ALTER SYSTEM SET recyclebin = ON;

How do you disable REcyclebin feature?
ALTER SESSION SET recyclebin = OFF;
ALTER SYSTEM SET recyclebin = OFF;

What are the views used to manage recyclebin?
USER_RECYCLEBIN- Information about recyclebin of a particular user
DBA_RECYCLEBIN - Information on all dropped objects in the recyclebin. This can be used by administrator

Does disabling purges all the objects already in the recyclebin?
Disabling the recycle bin does not purge or otherwise affect objects already in the recycle bin.

How do you recyclebin from SQL*PLUS prompt?
We can use the following SQL*PLUS command:
SQL> show recyclebin
ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME
---------------- ------------------------------ ------------ -------------------
EMPLOYEES BIN$yrMKlZaVMhfgNAgAIMenRA==$0 TABLE 2003-10-27:14:00:19

How to permanently remove objects from the recyclebin?
We can permanently remove the objects from the recyclebin using PURGE Command.Few examples as given below:
PURGE TABLE BIN$jsleilx392mk2=293$0;
PURGE TABLE int_admin_emp;
PURGE TABLESPACE example;
PURGE TABLESPACE example USER oe;
PURGE RECYCLEBIN;

How do we restore objects from a recyclebin?
Use the FLASHBACK TABLE ... TO BEFORE DROP statement to recover objects from the recycle bin.

Give an example for restoring objects from recyclebin?
FLASHBACK TABLE int_admin_emp TO BEFORE DROP
RENAME TO int2_admin_emp;
FLASHBACK TABLE BIN$yrMKlZaVMhfgNAgAIMenRA==$0 TO BEFORE DROP;

What happens to dependent objects when we restore them from recyclebin?
When you restore a table from the recycle bin, dependent objects such as indexes do not get their original names back; they retain their system-generated recycle bin names. We must manually rename dependent objects if we want to restore their original names. If we plan to manually restore original names for dependent objects, ensure that we make note of each dependent object's system-generated recycle bin name before you restore the table.

Give an example of restoring table and its dependent objects:-
1) After dropping JOB_HISTORY and before restoring it from the recycle bin, run the following query:

SELECT OBJECT_NAME, ORIGINAL_NAME, TYPE FROM RECYCLEBIN;
OBJECT_NAME ORIGINAL_NAME TYPE
------------------------------ ------------------------- --------
BIN$DBo9UChtZSbgQFeMiAdCcQ==$0 JHIST_JOB_IX INDEX
BIN$DBo9UChuZSbgQFeMiAdCcQ==$0 JHIST_EMPLOYEE_IX INDEX
BIN$DBo9UChvZSbgQFeMiAdCcQ==$0 JHIST_DEPARTMENT_IX INDEX
BIN$DBo9UChwZSbgQFeMiAdCcQ==$0 JHIST_EMP_ID_ST_DATE_PK INDEX
BIN$DBo9UChxZSbgQFeMiAdCcQ==$0 JOB_HISTORY TABLE

2) Restore the table with the following command:

FLASHBACK TABLE JOB_HISTORY TO BEFORE DROP;

3) Run the following query to verify that all JOB_HISTORY indexes retained their system-generated recycle bin names:

SELECT INDEX_NAME FROM USER_INDEXES WHERE TABLE_NAME = 'JOB_HISTORY';

INDEX_NAME
------------------------------
BIN$DBo9UChwZSbgQFeMiAdCcQ==$0
BIN$DBo9UChtZSbgQFeMiAdCcQ==$0
BIN$DBo9UChuZSbgQFeMiAdCcQ==$0
BIN$DBo9UChvZSbgQFeMiAdCcQ==$0

4) Restore the original names of the first two indexes as follows:

ALTER INDEX "BIN$DBo9UChtZSbgQFeMiAdCcQ==$0" RENAME TO JHIST_JOB_IX;
ALTER INDEX "BIN$DBo9UChuZSbgQFeMiAdCcQ==$0" RENAME TO JHIST_EMPLOYEE_IX;

Double quotes are required around the system-generated names.