Oracle Recycle Bin Usage

--Time: January 27, 2016
--author:zhenxing Yu

--Query Recycle Bin
SELECT * from RecycleBin;
SELECT * from User_recyclebin; --user_recyclebing and RecycleBin are synonyms, the fields are exactly the same

--Turn the Recycle Bin on or off
--A: Session level on/off
ALTER SESSION SET recyclebin = off/on

--b: System level on/off
ALTER SYSTEM SET recyclebin = off/on scope=spfile; --(need to restart the database)

--Query the table data in the Recycle Bin
SELECT * from "bin$klm0cy+qg0jgu3avgawe9w==$0";

--Empty the data in the Recycle Bin
PURGE TABLE bin$klm0cy+qg0jgu3avgawe9w==$0; --Clear according to object_name name
PURGE TABLE EMPLOYEES; --Clear according to Original_name name
PURGE RecycleBin; --Emptying the Recycle Bin data
PURGE recyclebin EXAMPLE USER SCOTT; --Empty the Recycle Bin data, but keep the Scott user's data

--Restore the data table in the Recycle Bin
FLASHBACK TABLE EMPLOYEES to before DROP; --Revert to the original table name
FLASHBACK TABLE EMPLOYEES to before DROP RENAME to Employees_new; --Revert to a new table name

--objects attached to the membership table
- Note : The table in the Recycle Bin, when the table is restored, the associated index will also be restored, but the name of the index will still be used in the Recycle Bin name
--so you need to rename the index so you know what each index means

--1, querying the index name of the recovered table
SELECT index_name, Index_type, TABLE_NAME, uniqueness
From User_indexes
WHERE table_name = ' EMP1 ';
--2, renaming an index
ALTER INDEX "bin$klnywcbgg1lgu3avgazi7q==$0" RENAME to Emp_n1;

Oracle Recycle Bin Usage