how to check recycle bin in oracle: Purge, enable/disable

In this post, we will discuss recycle bin in oracle, how to check recycle bin in oracle, how to check if recycle bin is enabled in oracle, how to purge recycle bin in oracle

What is recycle bin in oracle

• The Recycle Bin is a virtual container where all dropped objects reside.
• The dropped objects occupy the same space as when they were created.
• Once the tables are dropped any associated objects such as indexes, constraints, nested tables, and other dependent objects are prefixed with BIN$, and they are not moved.
• You can continue to access the data in a dropped table or even use Flashback Query against it. Each user has the same rights and privileges on Recycle Bin objects before they were dropped. You can view your dropped tables by querying the new RECYCLEBIN view.

how to check recycle bin in oracle

For a user

SELECT Object_Name, Original_Name, Type FROM User_Recyclebin;

For system-wide

SELECT Object_Name, Original_Name, Type FROM dba_Recyclebin;

how to check if recycle bin is enabled in oracle

We can check if recycle bin is enabled in oracle through the initialization parameter

sqlplus / as sysdba
show parameter recycle_bin

how to enable recycle bin in oracle

we can use the below command to enable the recycle bin

alter system set recycle_bin=on deffered;

This will enable recycle bin for new sessions

how to disable recycle bin in oracle

we can use the below command to disable the recycle bin

alter system set recycle_bin=off deffered;

This will disable recycle bin for new sessions

how to purge recycle bin in oracle

For a user

PURGE TABLE Employee; --Single Item Purge
PURGE recyclebin; --All Items Purge

For DBA users

PURGE TABLE SCOTT.Employee; --Single Item Purge 
PURGE dba_recyclebin; --All Items Purge across all the users
PURGE INDEX <INDEX Name> ;   --Purge the Index
PURGE TABLESPACE <Tablespace name>;  --Purge at the tablespace level
PURGE TABLESPACE <Tablespace name>  <User name>;  --Purge at the tablespace level at the particular user

How to get table back from recyclebin/How to flashback the drop the table

FLASHBACK TABLE  <table name> TO BEFORE DROP;
Example
FLASHBACK TABLE  TEST TO BEFORE DROP;

Related Articles

Recover drop/truncate/delete table on primary using flashback on standby database
Top 10 Useful Queries for Flashback Database
Oracle Flashback Database
oracle database administration tutorial
alter tablespace add datafile: Check out this post on How to add a datafile to tablespace in Oracle, add tempfile to the temporary tablespace, how to add a datafile in ASM
How to check Tablespace in Oracle: Learn about How to Check Tablespace in Oracle, tablespace free space, tablespace growth information, tablespace size , associated datafiles , tablespace size
how to change default tablespace in oracle: Default tablespace are the tablespace where the objects are created when no tablespace name is specified by users. Find out how to check default tablespace
How to check temp tablespace in Oracle: This article is about temp tablespace in Oracle, resize the tempfile, drop the tempfile, find the temp usage by Session
shrink datafile in Oracle: Check out how to shrink the datafile and reclaim space on the filesystem. How to resolve ORA-03297