DBA Notes: 2011/09/28

Cheng Li

How to calculate HWM and reclaim space

1.      How to calculate HWM

To manage space, Oracle Database tracks the state of blocks in the segment. The high water mark (HWM) is the point in a segment beyond which data blocks are unformatted and have never been used.

       MSSM (Manual segment space management) uses free lists to manage segment space. At table creation, no blocks in the segment are formatted. When a session first inserts rows into the table, the database searches the free list for usable blocks. If the database finds no usable blocks, then it preformats a group of blocks, places them on the free list, and begins inserting data into the blocks. In MSSM, a full table scan reads all blocks below the HWM.

       ASSM (auto segment space management) does not use free lists and so must manage space differently. When a session first inserts data into a table, the database formats a single bitmap block instead of preformatting a group of blocks as in MSSM. The bitmap tracks the state of blocks in the segment, taking the place of the free list. The database uses the bitmap to find free blocks and then formats each block before filling it with data. ASSM spread out inserts among blocks to avoid concurrency issues.

Solution 1):

SQL> SELECT BLOCKS FROM DBA_SEGMENTS

WHERE WNER=UPPER(APP) AND SEGMENT_NAME = UPPER(APP_TS_TX);

SQL> analyze table APP_TS_TX compute statistics; -- to collect information such as EMPTY_BLOCK

SQL> SELECT table_name,num_rows,blocks,empty_blocks FROM dba_tables  WHERE table_name='APP_TS_TX';

Thus, the tables' HWM = (query result 1) - (query result 2) - 1

NOTE: You can also use the DBMS_SPACE package and calculate the HWM = TOTAL_BLOCKS - UNUSED_BLOCKS - 1.

Solution 2)

SQL> select file_id,max(block_id+blocks-1) HWM
       from dba_extents
       group by file_id

2.      How to calculate the space can be reclaim from datafile

col name for a40
col resizecmd for a80

select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
       ceil(HWM * a.block_size)/1024/1024 ResizeTo,
       (a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,
       'alter database datafile '''||a.name||''' resize '||
       ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD
from v$datafile a,
     (select file_id,max(block_id+blocks-1) HWM
       from dba_extents
       group by file_id) b
where a.file# = b.file_id(+)
and (a.bytes - HWM *block_size)>0
order by 5

3. How to calculate the space can be reclaim from datafile for one tablespace
select a.file#,a.name,a.bytes/1024/1024 CurrentMB,
       ceil(HWM * a.block_size)/1024/1024 ResizeTo,
       (a.bytes - HWM * a.block_size)/1024/1024 ReleaseMB,
       'alter database datafile '''||a.name||''' resize '||
       ceil(HWM * a.block_size/1024/1024) || 'M;' ResizeCMD
from v$datafile a,
     (select file_id,max(block_id+blocks-1) HWM
       from dba_extents where file_id in
              (select b.file#  From v$tablespace a ,v$datafile b
                where a.ts#=b.ts# and a.name=' APP_TS_TX_DATA')
       group by file_id) b
where a.file# = b.file_id(+)
and (a.bytes - HWM *block_size)>0
order by 5

Reference:

http://avdeo.com/2008/02/21/reducing-datafile-size-to-recover-free-space-oracle-database-10g/

http://blog.csdn.net/wangsong145/article/details/3646059

http://biancheng.dnbcw.info/oracle/267339.html