FYI


http://download.oracle.com/docs/cd/B...21/tspaces.htm



Monitoring Free Space
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.