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:
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.