Home > Oracle > Recyclebin space re-claim

Recyclebin space re-claim


Recently there was a question put into me – do we need to setup a job to clean up the recylebin?  How frequently – yearly twice or four times in an year?  That was an interesting question!

Recylebin contains the list of objects dropped from the database, but the space is not re-used.  Once the available free space is over, Oracle start consuming the space from recylebin object storage – known as “space pressure”.  Here is a test, how recylebin space is getting re-used.

I have created a test tablespace with 2MB size and created few tables in it.

SQL> create tablespace mytbs datafile
‘/oradata/bmk/bmk/mytbs.dbf’ size 2m
extent management local segment space management auto;

Tablespace created.
SQL> select bytes/1024/1024 from dba_free_space where tablespace_name=’MYTBS’;

BYTES/1024/1024
—————
              1

SQL> create table mytab2 as select * from mytab1;

Table created.

SQL> create table mytab3 as select * from mytab1;

Table created.

SQL> create table mytab4 as select * from mytab1;

Table created.

SQL> create table mytab5 as select * from mytab1;

Table created.

SQL> select segment_name from dba_segments where  tablespace_name=’MYTBS’;

SEGMENT_NAME
——————–
MYTAB1
MYTAB2
MYTAB3
MYTAB4
MYTAB5

And now my tablespace is full, no space create new objects.

SQL> create table mybigtab as select  * from dba_objects;
create table mybigtab as select  * from dba_objects
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 12
ORA-01652: unable to extend temp segment by 8 in tablespace MYTBS

SQL>  select bytes/1024/1024 from dba_free_space where tablespace_name=’MYTBS’;

no rows selected
SQL> create table mytab6 as select * from mytab1;
create table mytab6 as select * from mytab1
                                     *
ERROR at line 1:
ORA-01658: unable to create INITIAL extent for segment in tablespace MYTBS

So, let me drop all my tables, and those tables will be automatically move to the recylebin.

SQL> drop table MYTAB1;

Table dropped.

SQL> drop table MYTAB2;

Table dropped.

SQL> drop table MYTAB3 ;

Table dropped.

SQL> drop table MYTAB4;

Table dropped.

SQL> drop table MYTAB5;

Table dropped.

SQL>  select bytes/1024/1024 from dba_free_space where tablespace_name=’MYTBS’;

BYTES/1024/1024
—————
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625

16 rows selected.

SQL> select segment_name from dba_segments where  tablespace_name=’MYTBS’;

SEGMENT_NAME
—————————————-
BIN$oDqsgmfsXmngRAADujMGXQ==$0
BIN$oDqsgmftXmngRAADujMGXQ==$0
BIN$oDqsgmfuXmngRAADujMGXQ==$0
BIN$oDqsgmfvXmngRAADujMGXQ==$0
BIN$oDqsgmfwXmngRAADujMGXQ==$0

 After dropping 5 tables, I can see free space is available in the tablespace and there are 5 objects in the recylebin.  That means I can start creating new objects in the tablespace. 

SQL> create table mynewtab as select * from user_objects;

Table created.

I just created a new table with few records, and I could see one out of 5 objects in the recylebin disappeared.

SQL> select segment_name from dba_segments where  tablespace_name=’MYTBS’;

SEGMENT_NAME
—————————————-
MYNEWTAB
BIN$oDqsgmftXmngRAADujMGXQ==$0
BIN$oDqsgmfuXmngRAADujMGXQ==$0
BIN$oDqsgmfvXmngRAADujMGXQ==$0
BIN$oDqsgmfwXmngRAADujMGXQ==$0

That means the space re-used from one table and is removed from the recylebin.  This is known as “space pressure” situation.  I started adding more records to the table, till the tablespace out of space.

 SQL>  insert into mynewtab select * from mynewtab;

2048 rows created.

SQL>  insert into mynewtab select * from mynewtab;

4096 rows created.

SQL>  insert into mynewtab select * from mynewtab;
 insert into mynewtab select * from mynewtab
*
ERROR at line 1:
ORA-01653: unable to extend table THOMAS.MYNEWTAB by 128 in tablespace MYTBS

SQL> select segment_name from dba_segments where  tablespace_name=’MYTBS’;

SEGMENT_NAME
—————————————-
MYNEWTAB

At the end, space from the recylebin completely re-claimed (infact there is no recylebin seperately, rather dropped object’s storage are just marked as dropped and kept aside, known as recylebin), no manual intervention is required.  So, recyclebin is completely managed by Oracle, when there is space pressure, the space will get re-used automatically. 

If you do not want to place objects in recylebin – you can use PURGE clause with the DROP command.  It will remove the table reference completely.

SQL> drop table mynewtab purge;

Table dropped.

SQL> select segment_name from dba_segments where  tablespace_name=’MYTBS’;

no rows selected

Also, you can turn off recylebin in database level by setting the parameter recyclebin

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE
———————————— ———– ———-

recyclebin                           string            on

 Let Oracle manage the recylebin, I felt never touch this – this is a nice feature to restore dropped objects.

Note : 1

You may use the below script to identify the space usage of a tablespace

select Tablespace_size_MB, free_space_KB, recyclebin_size_KB, Segment_size_KB
from
( select nvl(sum(bytes)/1024,0) free_space_KB from dba_free_space
  where tablespace_name = ‘&&Tablespace_name’),
  ( select sum(bytes)/1024/1024 Tablespace_size_MB from dba_data_files
      where tablespace_name = ‘&&Tablespace_name’),
  ( select nvl(sum(space) * 8,0) recyclebin_size_KB from dba_recyclebin
    where ts_name = ‘&&Tablespace_name’),
  (select sum(bytes)/1024 Segment_size_KB from dba_segments where tablespace_name = ‘&&Tablespace_name’)
 /

Where,

Tablespace_size_MB => Size of the tablespace in MB

free_space_KB            =>   Free space in out side recylebin

recyclebin_size_KB  =>  Freeable space in recylebin

 Segment_size_KB    => Current segment size in the tablespace

Advertisements
Categories: Oracle Tags: , ,
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: