Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Vladimer.

Asked: April 24, 2026 - 2:53 pm UTC

Last updated: April 28, 2026 - 4:22 am UTC

Version: 19.0.0.0.0

You Asked

I conducted an experiment with the recyclebin parameter.
First, I set recyclebin = OFF to observe the behavior of a regular DROP TABLE.

As I understand the architecture:
there is a tablespace and a segment, for example T1.Information about this segment is stored in the data dictionary.The tablespace also uses a space management mechanism (Segment Space Management AUTO), which tracks free blocks.

Therefore, when I drop a table with recyclebin = OFF,the entry about the segment is removed from the data dictionary,and all blocks of the segment are marked as free and can be reused by other objects.I performed this experiment and indeed observed exactly this behavior.

Next, I enabled the parameter recyclebin = ON.
In this case, the information about the table is not fully removed.The table is marked as inaccessible and renamed,after which it appears in the RECYCLEBIN view. At the same time, the segment continues to exist.

Then I read the following statement in the documentation:
Unless you specify the PURGE clause, the DROP TABLE statement does not result in space being released back to the tablespace for use by other objects, and the space continues to count toward the user's space quota.


However, in my experiment I observe the following behavior.
Suppose initially the tablespace had: 500 MB free space

I created a table and filled it with data totaling:200 MB

As a result:
free space = 300 MB
user quota used = 200 MB out of 500 MB

After that, I executed a regular: DROP TABLE table_name;

And I observed that: 200 MB returned to free space // free space became 500 MB again

At the same time:
the object still exists in the RECYCLEBIN
the segment size is approximately 200 MB
the user's quota still shows 200 MB used

I expected the free space to remain unchanged: 300 MB

because the documentation states that the space is not released for use by other objects.

Question:
Where is the flaw in my understanding of the logic?
Why does the free space in the tablespace increase even though the segment remains in the RECYCLEBIN and the user's quota is still consumed?

with LiveSQL Test Case:
https://freesql.com/library/tutorials/oracle-drop-table-without-purge-6h75CH?share_key=NIUQbjNr0e

and Connor said...

The rationale for what we are doing is that there is no guarantee that will *retain* an object in the recyclebin.

For example, consider the following timeline

tablespace is 1000M in size.
- 8 tables in there, each 100M in size, so the tablespace is 80% full.
- you drop table1 (with the recyclebin on)

*Nominally* you now have 700M used, and 300M "free" for new tables. To explain why its free:

- now you create a new table and populate it with 200M of data.
- you now have used 900M

- now you create a table and try to populate it with 100M of data.
- Even though the current state of play is:
- 900M used with 'real' data
- 100M used with recycle data
Your data population WILL work because we will *throwaway* the recyclebin objects to cater for the data in the new table.

So recycled space is indeed "free", because we *will* utilise it in the same way that "genuine" free space is used.

Rating

  (1 rating)

Comments

drop table without purge

Vladimer, April 28, 2026 - 7:31 am UTC

Thank you for your answer. This is exactly the behavior I observed in my experiment, but I could not properly explain why it was happening

I think the main reason for my confusion was this sentence from the documentation:
"Unless you specify the PURGE clause, the DROP TABLE statement does not result in space being released back to the tablespace for use by other objects, and the space continues to count toward the user's space quota"

Well, at the same time I watched how she freed herself automatically, as you said.

I interpreted this statement too literally, assuming that the space could not be reused by other objects at all while the segment still existed in the recycle bin

Your explanation makes it much clearer: the segment still exists and still counts toward the user’s quota, but the space is also reclaimable by Oracle if it is needed for new objects

Thank you very much for the explanation. I hope my question was not too silly compared to the others
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.