In large production databases, dropped tables are not immediately removed from the database. Instead, they are stored in the recycle bin so that they can be restored if required. However, when large objects remain in the recycle bin, they continue to consume significant tablespace storage. In this example we will demonstrate how to identify recycle bin objects and purge them in an Oracle 19c production database.
What is Recycle Bin in Oracle Database?
The Recycle Bin is a feature in Oracle Database that stores dropped database objects such as: Tables Indexes LOB segments Constraints Instead of permanently deleting these objects, Oracle renames them with a system-generated name starting with BIN$ and stores them in the recycle bin.Step 1: Connect to Database And Verify Database Status.
[oracle@exapocdbadm01 Scripts]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Tue Mar 3 11:15:08 2026
Version 19.28.0.0.0
Copyright (c) 1982, 2025, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.28.0.0.0
NAME OPEN_MODE FS_FAILOVER_STATUS FS_FAIL DATABASE_ROLE
--------- ------------- ---------------------- ------- ----------------
KHDB READ WRITE DISABLED PRIMARY
NAME OPEN_MODE START_TIME LOGINS INSTANCE_NAME HOST_NAME
--------- ------------- --------------------- ---------- ---------------- -----------------------------------
KHDB READ WRITE 14-JAN-2026 18:14:34 ALLOWED KHDB1 exapocdbadm01.oracledbasecrets.com
KHDB READ WRITE 14-JAN-2026 18:15:39 ALLOWED KHDB2 exapocdbadm02.oracledbasecrets.com
Step 2: Check Database Size and Tablespace Usage
Before purging recycle bin objects we check the overall database usage.
Checking Database Size Using SQL Script
SQL> @size
Database Size in GB
-------------------
193677.163
Database Size Used space Free space
-------------------- -------------------- --------------------
193677 GB 113193 GB 80484 GB
Status |Name |Size (GB) |Used (GB) |Free (GB) |(Used) %
---------|---------------|--------------|---------------|---------------|-----------
ONLINE |SYSTEM | 16.00| .71 | 15.29 | 4.46
ONLINE |SYSAUX | 16.00| 2.58 | 13.42 | 16.10
ONLINE |UNDOTBS1 | 512.00| 3.38 | 508.62 | 0.66
ONLINE |UNDOTBS2 | 16.00| .09 | 15.91 | 0.59
ONLINE |USERS | 1.00| .07 | 0.93 | 6.63
ONLINE |KHDBTBS | 32,760.00| 25076.84 | 7,683.16 | 76.55
ONLINE |KHDBIDXTBS | 32,548.00| 32408.29 | 139.71 | 99.57
ONLINE |DELTATBS | 17,548.00| 1526.92 | 16,021.08 | 8.70
ONLINE |GUGTBS | 31,548.00| 2985.11 | 28,562.89 | 9.46
ONLINE |MIMTBS | 17,548.00| 16674.89 | 873.11 | 95.02
ONLINE |OTHTBS | 29,548.00| 2898.48 | 26,649.52 | 9.81
ONLINE |TEMP | 512.00| 511.25 | 0.75 | 99.85
ONLINE |BIGTEMP | 30,720.00| 15381.63 | 15,338.38 | 50.07
13 rows selected.
From the above output we can see some tablespaces are highly utilized. Next we check large objects in the database including recycle bin objects.
Step 3: Identify Large Objects Including Recycle Bin Tables
Table Sizes for KHDBDBADMIN Schema
SQL> List Table Sizes
OWNER |TABLE_NAME | TABLE_PARTITIONS| TABLE_SIZE_GB| INDEX_SIZE_GB| TOTAL_SIZE_GB
-------------|--------------------|-----------------|--------------|----------------|-------------------
KHDBDBADMIN |MIMKHDB | 37| 16639.04| 0| 16639.04
KHDBDBADMIN |GUGEDRFLOW | 38| 2920.71| 12792.32| 15713.03
KHDBDBADMIN |OTHEDRFLOW | 221| 2838.12| 11860.39| 14698.51
KHDBDBADMIN |GUGFLOW_NEW | 33| 8508.89| 0| 8508.89
KHDBDBADMIN |DELTAEDRFLOW | 37| 1491.07| 6734.64| 8225.71
KHDBDBADMIN |MIMDEDRFLOW | 26| 4325.77| 0| 4325.77
KHDBDBADMIN |MIMCEDRFLOW | 26| 4296.79| 0| 4296.79
KHDBDBADMIN |MIMBEDRFLOW | 37| 4177.40| 0| 4177.40
KHDBDBADMIN |BIN$TBkq8CXkG17gY3oq| 31| 3822.40| 0| 3822.40
|GKzbbQ==$0 | | | |
KHDBDBADMIN |BIN$TBkq8CXlG17gY3oq| 1| 3821.51| 0| 3821.51
|GKzbbQ==$0 | | | |
KHDBDBADMIN |GUGFLOW_BATCH | 33| 1429.04| 0| 1429.04
KHDBDBADMIN |GUGFLOW_BATCH_NEW1 | 445| 208.08| 954.47| 1162.55
KHDBDBADMIN |OTHFLOW_BATCH | 27| 644.78| 0| 644.78
KHDBDBADMIN |MIMEDRFLOW_NEW | 58| 596.85| 0| 596.85
KHDBDBADMIN |STAGE_LOAD_OTH1 | 1| 389.98| 0| 389.98
KHDBDBADMIN |GUGFLOW_BATCH_NEW | 33| 200.09| 0| 200.09
KHDBDBADMIN |STAGE_LOAD_GUG1 | 1| 195.49| 0| 195.49
KHDBDBADMIN |MIMEDRFLOW_NEW2 | 1| 36.81| 0| 36.81
KHDBDBADMIN |BIN$S8yS4+XIgUvgY3oq| 32| 12.66| 0| 12.66
|GKw3lw==$0 | | | |
KHDBDBADMIN |BIN$S8yS4+XJgUvgY3oq| 2| 12.09| 0| 12.09
|GKw3lw==$0 | | | |
KHDBDBADMIN |BIN$S8yS4+XKgUvgY3oq| 2| 12.04| 0| 12.04
|GKw3lw==$0 | | | |
KHDBDBADMIN |BIN$S7efQhpnG9jgY3oq| 33| 0.26| 0| 0.26
|GKyq4A==$0 | | | |
KHDBDBADMIN |BIN$S8u2id1m77HgY3oq| 1| 0.01| 0| 0.01
|GKwDeg==$0 | | | |
KHDBDBADMIN |UPLOAD_DATA_HIST | 1| 0| 0| 0
KHDBDBADMIN |STAGE_LOAD_MIM1 | 1| 0| 0| 0
Notice the objects starting with BIN$. These are recycle bin objects. Large recycle bin objects can consume a huge amount of space. In this case more than 7 TB of data was present in the recycle bin.
Step 4: Purge Recycle Bin
To permanently remove recycle bin we use the following commands.
SQL> purge dba_recyclebin; DBA Recyclebin purged. SQL> purge recyclebin; Recyclebin purged.
To permanently remove recycle bin objects we use the following commands.
SQL> PURGE TABLE "BIN$abc12345==$0";
DBA Recyclebin purged.
The first command removes recycle bin objects for all users, while the second command removes objects for the current user.
Step 5: Verify Database Objects After Purge
Table Sizes for KHDBDBADMIN Schema
SQL> List Table Sizes
OWNER |TABLE_NAME | TABLE_PARTITIONS| TABLE_SIZE_GB| INDEX_SIZE_GB| TOTAL_SIZE_GB
-------------|--------------------|-----------------|---------------|---------------|---------------
KHDBDBADMIN |MIMKHDB | 37| 16639.04| 0| 16639.04
KHDBDBADMIN |GUGEDRFLOW | 38| 2920.71| 12792.32| 15713.03
KHDBDBADMIN |OTHEDRFLOW | 221| 2838.12| 11860.39| 14698.51
KHDBDBADMIN |GUGFLOW_NEW | 33| 8508.89| 0| 8508.89
KHDBDBADMIN |DELTAEDRFLOW | 37| 1491.07| 6734.64| 8225.71
KHDBDBADMIN |MIMDEDRFLOW | 26| 4325.77| 0| 4325.77
KHDBDBADMIN |MIMCEDRFLOW | 26| 4296.79| 0| 4296.79
KHDBDBADMIN |MIMBEDRFLOW | 37| 4177.4| 0| 4177.4
KHDBDBADMIN |GUGFLOW_BATCH | 33| 1429.04| 0| 1429.04
KHDBDBADMIN |GUGFLOW_BATCH_NEW1 | 445| 208.08| 954.47| 1162.55
KHDBDBADMIN |OTHFLOW_BATCH | 27| 644.78| 0| 644.78
KHDBDBADMIN |MIMEDRFLOW_NEW | 58| 596.85| 0| 596.85
KHDBDBADMIN |STAGE_LOAD_OTH1 | 1| 389.98| 0| 389.98
KHDBDBADMIN |GUGFLOW_BATCH_NEW | 33| 200.09| 0| 200.09
KHDBDBADMIN |STAGE_LOAD_GUG1 | 1| 195.49| 0| 195.49
KHDBDBADMIN |MIMEDRFLOW_NEW2 | 1| 36.81| 0| 36.81
KHDBDBADMIN |UPLOAD_DATA_HIST | 1| 0| 0| 0
KHDBDBADMIN |STAGE_LOAD_MIM1 | 1| 0| 0| 0
18 rows selected.
After purging the recycle bin, the BIN$ objects are removed from the database.
Why Purging Recycle Bin is Important
In large data warehouse or telecom databases, dropped partition tables can occupy several terabytes of space in the recycle bin. Purging these objects helps to:
- Free valuable tablespace storage
- Improve tablespace utilization
- Reduce unnecessary segment metadata
- Maintain database performance
Conclusion
The recycle bin is a powerful feature in Oracle Database that protects against accidental object deletion. However, it can also consume valuable storage if not managed properly. By using the PURGE RECYCLEBIN command and other purge options, DBAs can maintain database storage efficiently and keep the system clean. Regular monitoring and cleanup of the recycle bin is considered a best practice for database administrators.
Toufique Khan
No comments:
Post a Comment