How to calculate HWM and reclaim space

最新推荐文章于 2021-06-23 17:53:59 发布

转载最新推荐文章于 2021-06-23 17:53:59 发布 · 176 阅读

本文介绍了Oracle数据库中高水位线(HWM)的概念及其计算方法，并提供了两种计算HWM的具体SQL查询方案。同时，文章还详细说明了如何通过SQL查询来评估数据文件中可以回收的空间，包括对特定表空间的数据文件进行大小调整的方法。

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