如何降低表的HWM

(HWM=total_blocks-Unused Blocks+1)
可以在DUMP文件第一个EXTENT的前三个数据块中看到HWM.

释放在HWM之上的空闲BLOCK:
alter table table_name deallocate usused;

在HWM之下的空闲块:
1. 了解一个table在HWM下有多少blocks是不包含数据的.
analyze table &t_name compute statistics;
select (1-a.num/b.num_total)*100 as percent from 
(select count(distinct substr(rowid,1,15)) num from &t_name ) a,
(select blocks-empty_blocks num_total from user_tables wheretable_name='&t_name' )b 

在ASSM下,free space分别为0-25%,25-50%..BLOCK使用情况.
create or replace procedure show_space_assm(
p_segname in varchar2,
p_owner in varchar2 default user,
p_type in varchar2 default 'TABLE')
as 
l_fs1_bytes number; 
l_fs2_bytes number; 
l_fs3_bytes number; 
l_fs4_bytes number; 
l_fs1_blocks number; 
l_fs2_blocks number; 
l_fs3_blocks number; 
l_fs4_blocks number; 
l_full_bytes number; 
l_full_blocks number; 
l_unformatted_bytes number; 
l_unformatted_blocks number; 

procedure p(p_label in varchar2,p_num in number)
is 
begin 
dbms_output.put_line(rpad(p_label,40,'.') || p_num );
end;

begin 
dbms_space.space_usage( 
segment_owner => p_owner, 
segment_name => p_segname, 
segment_type => p_type, 
fs1_bytes => l_fs1_bytes, 
fs1_blocks => l_fs1_blocks, 
fs2_bytes => l_fs2_bytes, 
fs2_blocks => l_fs2_blocks, 
fs3_bytes => l_fs3_bytes, 
fs3_blocks => l_fs3_blocks, 
fs4_bytes => l_fs4_bytes, 
fs4_blocks => l_fs4_blocks, 
full_bytes => l_full_bytes, 
full_blocks => l_full_blocks, 
unformatted_blocks => l_unformatted_blocks, 
unformatted_bytes => l_unformatted_bytes ); 
p('free space 0-25% blocks:',l_fs1_blocks);
p('free space 25-50% blocks:',l_fs2_blocks);
p('free space 50-75% blocks:',l_fs3_blocks);
p('free space 75-100% blocks:',l_fs4_blocks);
p('Full Blocks:','l_full_blocks);
p('Unformatted blocks:',l_unformatted_blocks);
end; 
/

降低HWM的方法:
(一) MOVE (need rebuild all indexes,有DLL锁,需要两倍空间)
alter table TABLE_NAME move;
alter table TABLE_NAME move tablespace TABLESPACE_NAME;

(二) DBMS_REDEFINITION 联机重定义

Oracle 9i 之后可以进行联机重定义表(Online Redefine Tables,或"在线重定义表").该特性从某种程度上提供了一定的高可用性.通过该功能可以做到:

修改表的存储参数
移动该表到相同 Schema 下的 不同表空间内
添加并行查询支持
添加或删除分区
重建表以便减少碎片
在普通表和索引组织(index-organized)表之间互相转换
添加或删除列
做一个从普通表到分区表之间的转换操作.可以用 DBA 用户操作.如果是普通用户需要有DBMS_REDEFINITION 包的可执行权限以及如下权限:

* CREATE ANY TABLE 
* ALTER ANY TABLE 
* DROP ANY TABLE 
* LOCK ANY TABLE 
* SELECT ANY TABLE 下面通过一个例子来简单演练一把.假定目前产品库有一个非分区表 TEST. 准备对把该表联机修改为分区表.

我们的测试表结构(ID列为PK):

SQL> DESC TEST
Name Null? Type
----------------------------------------- -------- -------------------
ID NOT NULL VARCHAR2(16)
USER_NAME VARCHAR2(16)
GMT NOT NULL TIMESTAMP(6)
1) 验证该表是否可以进行联机重定义(如果不可以会给出具体原因):

SQL> execDBMS_REDEFINITION.can_REDEF_TABLE('SCOTT','TEST',1);

2) 创建中间临时表并划分分区: 

CREATE TABLE test_p(
ID VARCHAR2(16) NOT NULL,
user_name VARCHAR2(16),
gmt TIMESTAMP(6) NOT NULL
)
PARTITION BY RANGE (gmt) (
PARTITION test_p200511 VALUES LESS THAN(TO_DATE('2005-12-01','yyyy-mm-dd')) ,
PARTITION test_p200512 VALUES LESS THAN(TO_DATE('2006-01-01','yyyy-mm-dd')));3) 开始重定义表 

BEGIN
DBMS_REDEFINITION.start_redef_table
('SCOTT','TEST','TEST_P',
'ID ID,
USER_name USER_name,
GMT GMT',
DBMS_REDEFINITION.cons_use_pk
);
END;
-- 注意条件dbms_redefinition.cons_use_pk, 如果是根据 ROWID 做联机重定义,则用 dbms_redefinition.cons_use_rowid .

4) 创建索引限制以及触发器等.注:在10g 中,如果这些定义变化了.可以通过DBMS_REDEFINITION.REGISTER_DEPENDENT_OBJECT() 来创建.如果这些对象定义不变化,则调用DBMS_REDEFINITION.COPY_TABLE_DEPENDENTS() 即可.

ALTER TABLE test_p
ADD CONSTRAINT test_p_pk PRIMARY KEY (ID)
USING INDEX
TABLESPACE indx;
5) 同步表内容 . 如果需要同步的数据特别大,则可能考虑通过 prebuilt table 的方法先建立物化视图. 
EXEC DBMS_REDEFINITION.sync_interim_table ('SCOTT', 'test', 'test_p');
6) 结束重定义过程
EXEC DBMS_REDEFINITION.finish_redef_table ('scott', 'test', 'test_p');
7) 删除临时表 TEST_P.

drop table test_p;
*) 如果想中途停止重定义过程,则通过如下指令退出该过程:

EXEC dbms_redefinition.abort_redef_table('SCOTT','test','test_p');
进一步熟悉该过程的机理可以通过查询:

SELECT sql_text FROM v$sqlarea WHERE sql_text LIKE'%TEST_P%';联机重定义表必须考虑的问题: 会占用源表两倍的空间.此外还要考虑物化视图 Log 的空间以及带来的其他开销.

(三)Shrink (10G新特性)
alter table TABLE_NAME enable row movement;
alter table TABLE_NAME shrink space;
优点: 不需要REBUILD INDEX ,不需要更大空间.没有对表LOCK;