Monday, 24 June 2013

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

No comments:

Post a Comment