Recyclebin retention policy in Oracle

We know that Oracle 10 Gb introduces the recyclebin concept. When we delete a table without specifying purge, the system only renames the table to the name starting with Bin $, modify related data in the data dictionary.
In the Administrator's Guide, recyclebin is described as follows: Recycle Bin is actually a data dictionary table that contains information about the deleted object. The deleted table and related objects (such as indexes, constraints, and nested tables) are not removed and still occupy space. They will continue to use the user's space quota until they are explicitly cleared from the recycle bin, or, in another rare case, the database must clear them due to the space limitations of the tablespace.
From this we can know that if the recyclebin function is enabled after Oracle 10 Gb, when you drop a table, it will still occupy the original space.
We can use user_recyclebin or dba_recyclebin to view the object information in the recycle bin, or use recyclebin, which is a public synonym for user_recyclebin.
As mentioned in the document, except for manual purge, objects in these recycle bin will be cleared only when there is insufficient space in the tablespace. We can perform a test (the test environment is rac10.2.0.1 + ASM)

Create a tablespace and give it 20 mb of capacity

SQL> create tablespace test1 datafile size 20m;Tablespace created.

Then we create a test table A in the tablespace.

SQL> create table w1.a tablespace test1 as select * from dba_objects;Table created.SQL> select owner,segment_name,round(bytes/1024/1024,2)||' MB' m from dba_segments where tablespace_name='TEST1';OWNER SEGMENT_NAME M------------ ---------------------------------------------------------------------------------------W1 A 6 MBSQL>

One Table occupies 6 MB of space. We will create two more test tables.

SQL> create table w1.b tablespace test1 as select * from dba_objects;Table created.SQL> create table w1.c tablespace test1 as select * from dba_objects;Table created.SQL> select round(sum(bytes)/1024/1024,2)||' MB' from dba_segments where tablespace_name='TEST1';ROUND(SUM(BYTES)/1024/1024,2)||'MB'-------------------------------------------18 MB

At this time, the tablespace is 18 MB. In this case, all three tables are deleted.

SQL> drop table w1.a;Table dropped.SQL> drop table w1.b;Table dropped.SQL> drop table w1.c;Table dropped.SQL> select owner,object_name,original_name from dba_recyclebin where ts_name='TEST1';OWNER OBJECT_NAME------------------------------ ------------------------------ORIGINAL_NAME--------------------------------W1 BIN$r6HZooW/xpzgQKjAb01Spw==$0BW1 BIN$r6HZooW+xpzgQKjAb01Spw==$0AW1 BIN$r6HZooXAxpzgQKjAb01Spw==$0CSQL> col owner format a4SQL> col segment_name format a35SQL> col m format a10SQL> select owner,segment_name,round(bytes/1024/1024,2)||' MB' m from dba_segments where tablespace_name='TEST1';OWNE SEGMENT_NAME M---- ----------------------------------- ----------W1 BIN$r6HZooXAxpzgQKjAb01Spw==$0 6 MBW1 BIN$r6HZooW+xpzgQKjAb01Spw==$0 6 MBW1 BIN$r6HZooW/xpzgQKjAb01Spw==$0 6 MBSQL>

As you can see, the three tables are moved to the recycle bin, and the space is not released.
At this time, the 20 mb tablespace is available for 2 MB. What if I create another table with the same name?

SQL> alter session set tracefile_identifier='rctest';Session altered.SQL> alter session set sql_trace=true;Session altered.SQL> create table w1.d tablespace test1 as select * from dba_objects;Table created.SQL> alter session set sql_trace=false;Session altered.SQL> select owner,object_name,original_name from dba_recyclebin where ts_name='TEST1';OWNE OBJECT_NAME ORIGINAL_NAME---- ------------------------------ --------------------------------W1 BIN$r6HZooW/xpzgQKjAb01Spw==$0 BW1 BIN$r6HZooXAxpzgQKjAb01Spw==$0 C

We can see that table A is killed. Let's see what we can find in the trace file.
Before executing create table, the system first queries whether the table space in test1 is online. When executing create table, first check whether the same name already exists in the same namespace. Next, update the relevant data dictionary and prepare to insert data. What should I do if I find that there is not enough space? Note the following information:

Select OBJ #, type #, flags, related, Bo, purgeobj, con #
From
Recyclebin $ where ts # =: 1 and to_number (bitand (flags, 16) = 16 order
By dropscn

Note This sorting: Order by dropscn

What Does Oracle do next:

Drop table "W1". "bin $ r6hzoow + xpzgqkjab01spw = $0" purge

After the table is deleted, update the related data dictionary and insert new data.

 

We can conclude that when deleting a table, if purge is not specified, the table will be placed in the recycle bin. When creating a new segment, if there is not enough space in the tablespace, Oracle will delete some objects from the recycle bin in the order of dropscn. If autoextend is specified for the data file, the priority is to delete the objects in the recycle bin before expanding the data file.

When deleting a user, the system first purge the table from the recycle bin, and then uses the following DELETE command for the table under the user

Drop table "W1". "D" cascade constraints purge force

Then release the occupied space.