调整表的HWM

一、查看表实际使用情况

DECLARE
v_sql   VARCHAR2(1000);
v_used  NUMBER;
v_total NUMBER;
BEGIN
FOR cur IN (SELECT *
FROM (SELECT a.owner, a.segment_name,
round(SUM(a.bytes) / 1024 / 1024 / 1024) total_byte
 FROM dba_segments a
WHERE a.tablespace_name = 'TBS_NOPART_11'
                          AND a.segment_name<>'ODS_CUST_GBCALLCDR_D_COPY'
GROUP BY a.owner, a.segment_name
ORDER BY round(SUM(a.bytes) / 1024 / 1024 / 1024) DESC)
 WHERE rownum <= 10)
LOOP
v_sql := 'SELECT /*+ parallel(a)*/ COUNT(DISTINCT dbms_rowid.rowid_block_number(ROWID) ||
 dbms_rowid.rowid_relative_fno(ROWID)) 
 FROM ' || cur.owner || '.' || cur.segment_name || ' a';
EXECUTE IMMEDIATE v_sql
INTO v_used;
SELECT SUM(a.blocks)
INTO v_total
FROM dba_segments a
 WHERE a.segment_name = cur.segment_name
 AND a.owner = cur.owner;
dbms_output.put_line(cur.owner || '.' || cur.segment_name || ' 总大小 ' ||
 cur.total_byte || ' total_blocks ' || v_total ||
 ' used_blocks ' || v_used);
END LOOP;
END;

二、调整表HWM的几种方法。

1、导出、导入数据

exp/imp expdp/impdp

2、TABLE MOVE(9I)

alter table table_name move ...

aleter table move 只是给表中所有数据搬到新的存储空间上,就相当于把A房间内乱七八糟的东西整理起来放到B房间里面然后再把A房间让出来给别人,这样你不但让出了整个A房间而且仍然只占用一个房间,但是你的东西(数据)也整齐了可能只占用B房间很少的一部分
也在同一表空间move——效果很好必须重建索引 ， 可以直接在本tbs上．　eg: alter table table_name move; 需要对index rebuild ;

3、SHRINK SPACE(10G)

alter table <tablename> enable row movement;
alter table <tablename> shrink space;

在10g以前的版本，重置Reset高水位HWM的传统方法就是将表truncate以后然后再把数据reload进行。10g版本提供更加快捷方便的方法，通过使用命令alter table <xxx> shrink space，我们能够很快回收浪费的空间，同时重置高水位HWM。但是必须注意在采用这个命令的前提条件：

1、表所在的表空间运行于ASSM（Auto Segment Space Management）管理模式.

2、必须开启表的Row Movement

   一旦满足以上条件，可以运行：

ALTER TABLE <TABLE-NAME> SHRINK SPACE CASCADE;

If you just want to only defrag the table and not reset the HWM or reclaim the space due to active dml transactions, you can use

在DML事务较多的情况下，如果你只是想DEFRAG表，暂时不重置HWM和回收空间，可以使用

ALTER TABLE <TABLE-NAME> SHRINK SPACE CASCADE COMPACT;

 

In order to see how many empty blocks are still there in the table, first analyze the table and then you can use

以下语句可以查看表有多少空闲块，先执行表分析，然后使用：

select table_name, blocks, empty_blocks, last_analyzed, global_stats from user_tables where table_name=<TABLE-NAME>;

 

A point to be noted here is that the BLOCKS/EMPTY_BLOCKS being reported in the DBA_TABLES holds good for tables created in tablespaces with BMB segments and not freelist segments. For tablespaces in freelist segments, usage of DBMS_SPACE is recommended.