Coalescing Free Space

This example was tested on an Oracle 9 database. It shows the coalescing of free space in a dictionary managed tablespace where pctincrease=0:

1. When new extents have to be allocated.
2. When the alter tablespace <<tablespace_name>> coalesce command is run.

First create a tablespace:

SQL> conn / as sysdba
Connected.
SQL> create tablespace andrews_tablespace
  2  extent management dictionary
  3  datafile '/test9/datafiles/andrew.dbf'
  4  size 10 m
  5  default storage(pctincrease 0)
  6  /

Tablespace created.

SQL>

Create a map of the tablespace. It will have a single free area:

SQL> select file_id, block_id,
  2  substr(segment_name,1,20) Object_Name, bytes
  3  from dba_extents
  4  where tablespace_name = 'ANDREWS_TABLESPACE'
  5  union
  6  select file_id, block_id, 'FREE', bytes
  7  from dba_free_space
  8  where tablespace_name = 'ANDREWS_TABLESPACE'
  9  order by file_id, block_id
 10  /

   FILE_ID   BLOCK_ID OBJECT_NAME               BYTES
---------- ---------- -------------------- ----------
        25          2 FREE                   10481664

SQL>

Create a table in the tablespace:

SQL> create table andrews_table1(col1 number)
  2  tablespace andrews_tablespace
  3  storage (initial 2m next 2m minextents 4)
  4  /

Table created.

SQL> 

Create a map of the tablespace. This will show the new table's 4 extents and a small free area at the end of the tablespace:

SQL> select file_id, block_id,
  2  substr(segment_name,1,20) Object_Name, bytes
  3  from dba_extents
  4  where tablespace_name = 'ANDREWS_TABLESPACE'
  5  union
  6  select file_id, block_id, 'FREE', bytes
  7  from dba_free_space
  8  where tablespace_name = 'ANDREWS_TABLESPACE'
  9  order by file_id, block_id
 10  /

   FILE_ID   BLOCK_ID OBJECT_NAME               BYTES
---------- ---------- -------------------- ----------
        25          2 ANDREWS_TABLE1          2109440
        25        517 ANDREWS_TABLE1          2109440
        25       1032 ANDREWS_TABLE1          2109440
        25       1547 ANDREWS_TABLE1          2109440
        25       2062 FREE                    2043904

SQL>

Drop the table:

SQL> drop table andrews_table1
  2  /

Table dropped.

SQL> 

Create a map of the tablespace. This will show 5 free areas:

SQL> select file_id, block_id,
  2  substr(segment_name,1,20) Object_Name, bytes
  3  from dba_extents
  4  where tablespace_name = 'ANDREWS_TABLESPACE'
  5  union
  6  select file_id, block_id, 'FREE', bytes
  7  from dba_free_space
  8  where tablespace_name = 'ANDREWS_TABLESPACE'
  9  order by file_id, block_id
 10  /

   FILE_ID   BLOCK_ID OBJECT_NAME               BYTES
---------- ---------- -------------------- ----------
        25          2 FREE                    2109440
        25        517 FREE                    2109440
        25       1032 FREE                    2109440
        25       1547 FREE                    2109440
        25       2062 FREE                    2043904

SQL>

Create a table in the tablespace. Its extents are 3 megabytes each i.e. they are bigger than any of the free areas in the tablespace so Oracle has to do some coalescing first:

SQL> create table andrews_table2(col1 number)
  2  tablespace andrews_tablespace
  3  storage (initial 3m next 3m minextents 3)
  4  /

Table created.

SQL> 

Create a map of the tablespace. This shows the new table made up of 3 megabyte extents plus a free area at the end of the tablespace:

SQL> select file_id, block_id,
  2  substr(segment_name,1,20) Object_Name, bytes
  3  from dba_extents
  4  where tablespace_name = 'ANDREWS_TABLESPACE'
  5  union
  6  select file_id, block_id, 'FREE', bytes
  7  from dba_free_space
  8  where tablespace_name = 'ANDREWS_TABLESPACE'
  9  order by file_id, block_id
 10  /

   FILE_ID   BLOCK_ID OBJECT_NAME               BYTES
---------- ---------- -------------------- ----------
        25          2 ANDREWS_TABLE2          3153920
        25        772 ANDREWS_TABLE2          3153920
        25       1542 ANDREWS_TABLE2          3153920
        25       2312 FREE                    1019904

SQL>

Drop the table:

SQL> drop table andrews_table2
  2  /

Table dropped.

SQL>

Create a map of the tablespace. This shows 3 new free areas previously occupied by ANDREWS_TABLE2:

SQL> select file_id, block_id,
  2  substr(segment_name,1,20) Object_Name, bytes
  3  from dba_extents
  4  where tablespace_name = 'ANDREWS_TABLESPACE'
  5  union
  6  select file_id, block_id, 'FREE', bytes
  7  from dba_free_space
  8  where tablespace_name = 'ANDREWS_TABLESPACE'
  9  order by file_id, block_id
10  /

   FILE_ID   BLOCK_ID OBJECT_NAME               BYTES
---------- ---------- -------------------- ----------
        25          2 FREE                    3153920
        25        772 FREE                    3153920
        25       1542 FREE                    3153920
        25       2312 FREE                    1019904

SQL> 

Finally, show the effect of coalescing the tablespace manually:

SQL> alter tablespace andrews_tablespace coalesce
  2  /

Tablespace altered.

SQL>

Create a map of the tablespace. It will have 1 single free area created by the merging of the 4 free areas which were there before:

SQL> select file_id, block_id,
  2  substr(segment_name,1,20) Object_Name, bytes
  3  from dba_extents
  4  where tablespace_name = 'ANDREWS_TABLESPACE'
  5  union
  6  select file_id, block_id, 'FREE', bytes
  7  from dba_free_space
  8  where tablespace_name = 'ANDREWS_TABLESPACE'
  9  order by file_id, block_id
 10  /

   FILE_ID   BLOCK_ID OBJECT_NAME               BYTES
---------- ---------- -------------------- ----------
        25          2 FREE                   10481664

SQL> 

Drop the tablespace:

SQL> drop tablespace andrews_tablespace
  2  including contents and datafiles
  3  /

Tablespace dropped.

SQL>