Oracle Recycle Bin. Why DROP TABLE doesn’t free the space.

The class

What happened in class, step by step

The commands and output exactly as they appeared on the classroom screen.

The lesson plan on the whiteboard

Chapter 12, DDL statements. The topic: the DROP TABLE statement, first on the board, then tried live on the screen.

• DROP TABLE renames the table to a BIN$ name and moves it to the recycle bin
• Flashback Drop, the feature that brings it back (since Oracle 10g)
• DROP TABLE … PURGE bypasses the recycle bin and drops the table permanently
• SHOW RECYCLEBIN, and DESC USER_RECYCLEBIN
• Manual space reclamation: PURGE TABLE and PURGE RECYCLEBIN
• FLASHBACK TABLE name TO BEFORE DROP [RENAME TO new_name]

1. Fill the tablespace: two 36 MB tables

   TEST_BIN is a 100 MB tablespace that can autoextend, but only up to 100 MB, so it cannot simply grow. Two copies of the SALES table take 36 MB each, and user_free_space reports 21.94 MB free. A third 36 MB table would not fit.

   SQL> create table bin_table2 tablespace test_bin as select * from sales;
   
   Table created.
   
   SQL> select segment_name,bytes/1024/1024 as mb from user_segments where tablespace_name = 'TEST_BIN';
   
   SEGMENT_NAME                           MB
   ------------------------------ ----------
   BIN_TABLE                              36
   BIN_TABLE2                             36
   
   SQL> select tablespace_name,sum(bytes/1024/1024) mb from user_free_space
     2  where tablespace_name = 'TEST_BIN' group by tablespace_name;
   
   TABLESPACE_NAME                        MB
   ------------------------------ ----------
   TEST_BIN                          21.9375

2. Drop both tables, without PURGE

   Without PURGE, DROP TABLE does not delete the table. Oracle renames it to a system-generated BIN$ name and moves it, with its indexes, triggers and constraints, into the recycle bin. That is the point of the exercise: Purge nahi lagana hai, because recycle bin mein jaana chahiye. (“Don’t add PURGE, because it should go to the recycle bin.”)

   SQL> drop table bin_table;
   
   Table dropped.
   
   SQL> drop table bin_table2;
   
   Table dropped.

3. Oracle now says 93.94 MB is free

   The same query now reports 93.94 MB free. The 72 MB the two tables still occupy is counted as free, because Oracle can take it back whenever it needs to. Oracle is claiming ki itni jagah free hai. But technically itni jagah free nahi hai… (“Oracle is claiming this much space is free. But technically, this much space is not free.”)

   SQL> select tablespace_name,sum(bytes/1024/1024) mb from user_free_space
     2  where tablespace_name = 'TEST_BIN' group by tablespace_name;
   
   TABLESPACE_NAME                        MB
   ------------------------------ ----------
   TEST_BIN                          93.9375

4. The tables are still there

   SHOW RECYCLEBIN lists both, under their BIN$ names, with the second each was dropped. Until they are purged, either one comes back with a single command: FLASHBACK TABLE bin_table TO BEFORE DROP;

   SQL> show recyclebin
   ORIGINAL NAME    RECYCLEBIN NAME   OBJECT TYPE  DROP TIME
   ---------------- ----------------- ------------ -------------------
   BIN_TABLE        BIN$…==$0         TABLE        2025-12-09:16:21:25
   BIN_TABLE2       BIN$…==$0         TABLE        2025-12-09:16:21:27

   Recycle bin names are shortened here. Oracle gives every dropped object its own unique BIN$ name.

FAQ

What students ask about the recycle bin

Your trainer

Saurabh Joshi

Saurabh Joshi has taught at Unisoft since 17 May 2010, 16 years now. He teaches SQL and PL/SQL, Advanced Excel and data analytics, and is Unisoft’s CTO. His six Credly badges include Oracle Database SQL Certified Associate.

This clip is how his classes run: the lesson plan on the whiteboard first, then the same idea tried live in SQL*Plus, with the real output on the screen.

• Oracle SQL certified
• 16+ years of teaching
• SQL · Excel · Tableau

His profile and reviews The SQL course

In his students’ words

From Unisoft’s Google listing, as the students wrote them. Each card opens the review on Google.

• Saurabh Joshi sir is my oracle SQL trainer, he is highly knowledgeable person with 15 years of teaching experience. Sir's experience and knowledge tells a lot about him , it's just like they know answer of every question. …

• …Attended sql12c training taken by mr.saurabh joshi, very experienced trainer explains concepts very well. Saurabh sir is very motivating . he was the best teacher unisoft have. …

• Saurabh Joshi sir for SQL is the best trainer.. and he is always motivate and co-operate with us and he changed my mind set and always said that you can do any thing only work hard...