Monitoring Free Space in a Tablespace

You can use the following views for monitoring free space in a tablespace:

• DBA_FREE_SPACE 
• DBA_FREE_SPACE_COALESCED 

The following statement displays the free space in tablespace tabsp_4:

SELECT BLOCK_ID, BYTES, BLOCKS FROM DBA_FREE_SPACE WHERE TABLESPACE_NAME = 'TABSP_4' ORDER BY BLOCK_ID; 

BLOCK_ID BYTES BLOCKS

  ---------- ---------- ---------- 

  2                16384                  2 

  4                16384                  2 

  6                81920                10 

  16             16384                   2

  27             16384                   2 

 29              16384                   2

 31              16384                   2 

33               16384                   2 

35               16384                  2

37               16384                  2

39               8192                    1 

40               8192                    1

41             196608                  24

---

13 rows selected. 

This view shows that there is adjacent free space in tabsp_4 (for example, blocks starting with BLOCK_IDs 2, 4, 6, 16) that has not been coalesced. After coalescing the tablespace using the 

ALTER TABLESPACE statement shown previously, the results of this query would read: 

BLOCK_ID BYTES BLOCKS 

  ---------- ---------- ----------

  2             131072         16

  27          311296           38 

--

  2 rows selected.
The DBA_FREE_SPACE_COALESCED view displays statistics for coalescing activity. It is also useful in determining if you need to coalesce space.
Thanking you
---
regards
Mohammad Shahnawaz
Oracle's Passion
Kuwait