How to check recycle bin in Oracle: Purge, enable/disable

The concept of a “Recycle Bin” plays a crucial role in data recovery and management. This feature, introduced in Oracle 10g, is akin to the recycle bin in Windows or the trash in macOS, providing a safety net for data that has been deleted. In this article, we will discuss the recycle bin in Oracle, How to check the recycle bin in Oracle, how to check if the recycle bin is enabled in Oracle, how to purge the recycle bin in Oracle

What is the 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 Does it Work?

When an object (like a table or index) is dropped, Oracle renames the object with a system-generated name and places it in the Recycle Bin. This renamed object is known as a “dropped object”. It’s important to note that while in the Recycle Bin, these objects continue to occupy space and can impact database storage.

How to check the 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 the recycle bin is enabled in Oracle

We can check if the 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 a 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 the 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 recycle bin/How to flashback the drop the table

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

How to check recycle bin size in Oracle

SELECT owner,
original_name,
object_name,
ts_name,
space AS space_blks,
ROUND((space*8)/1024,2) space_mb
FROM dba_recyclebin
WHERE owner = '&1;
ORDER BY 1, 2;

Here 8 specify the 8KB block. If you have 16KB block, then the query will be

SELECT owner,
original_name,
object_name,
ts_name,
space AS space_blks,
ROUND((space*16)/1024,2) space_mb
FROM dba_recyclebin
WHERE owner = '&1;
ORDER BY 1, 2;

You can do group by on the owner, tablespace to get an aggregated size

SELECT owner,sum(ROUND((space*8)/1024,2)) Total_space_mb FROM dba_recyclebin WHERE group by owner;
SELECT ts_name,sun(ROUND((space*8)/1024,2)) Total_space_mb FROM dba_recyclebin WHERE group by ts_name;

The Recycle Bin feature in Oracle databases offers a significant advantage in data management and recovery. By understanding its functionality and effectively managing its use, database administrators and users can enhance data protection and streamline recovery processes

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