这里简单地讲述回收表的高水位，从表对应的段包含的块数的变化，间接地表现出高水位线
对表记录检索的影响。在这个表高水位回收的测试中，也顺便验证了表中的索引在回收高水位的过程中
并没有发生失效。还有就是在高水位回收前，需要配置表的行移动功能，不然就不能正常进行高水位的回收。
--创建测试表：
suxing@PROD> create table emp tablespace users as select * from hr.employees where 1=0;
Table created.

--给测试表创建索引：
suxing@PROD> create index idx_emp_salary on emp(salary) tablespace users;
Index created.

--查看索引的状态：
suxing@PROD>select TABLE_OWNER,TABLE_NAME,INDEX_NAME,INDEX_TYPE,STATUS,INCLUDE_COLUMN
2 from user_indexes
3 where table_name ='EMP';
TABLE_OWNER TABLE_NAME INDEX_NAME INDEX_TYPE STATUS INCLUDE_COLUMN
------------------------------ ------------------------------ ------------------------------ --------------------------- -------- --------------
SUXING EMP IDX_EMP_SALARY NORMAL VALID

--插入大量数据记录：
begin
for i in 1..500 loop
insert into emp select * from hr.employees;
end loop;
commit;
end;
/

--查看数据：
suxing@PROD>select count(*) from emp;
COUNT(*)
----------
53500
#一般表中的数据记录多，测试效果才明显。

--查看索引状态：
suxing@PROD>select INDEX_NAME,STATUS from user_indexes where INDEX_NAME=upper('idx_emp_salary');
INDEX_NAME STATUS
------------------------------ --------
IDX_EMP_SALARY VALID

--查看表的信息：
SELECT num_rows, blocks, empty_blocks as empty, avg_space, chain_cnt, avg_row_len
FROM user_tables where table_name = 'EMP';
suxing@PROD>SELECT num_rows, blocks, empty_blocks as empty, avg_space, chain_cnt, avg_row_len
FROM user_tables where table_name = 'EMP';
NUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ---------- -----------

--收集表的统计信息：
suxing@PROD>exec dbms_stats.gather_table_stats(user,'EMP');
PL/SQL procedure successfully completed.

--或者：
analyze table emp compute statistics for table for all columns for all indexes;
#可以通过多种方法对表进行表分析。

suxing@PROD>SELECT num_rows, blocks, empty_blocks as empty, avg_space, chain_cnt, avg_row_len
2 FROM user_tables where table_name = 'EMP';
--查看表emp的基本信息：
NUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ---------- -----------
53500 622 18 1074 0 71

suxing@PROD>select segment_name,tablespace_name,bytes,blocks
2 from user_segments
3 where segment_name='EMP';
SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS
-------------------- ------------------------------ ---------- ----------
EMP USERS 5242880 640

--查看表的执行计划的统计信息：
suxing@PROD>set autot trace stat
suxing@PROD>select count(*) from emp;
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
569 consistent gets
0 physical reads
0 redo size
527 bytes sent via SQL*Net to client
523 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
#也可以从查询SQL的执行计划中去判断回收高水位对表检索的影响。

--删除大部分记录：
suxing@PROD>delete emp where rownum<=50000;
50000 rows deleted.
Statistics
----------------------------------------------------------
16 recursive calls
155833 db block gets
546 consistent gets
0 physical reads
27218000 redo size
840 bytes sent via SQL*Net to client
786 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
50000 rows processed
suxing@PROD>commit;
Commit complete.

--删除数据记录后再次收集表的统计信息：
suxing@PROD>exec dbms_stats.gather_table_stats(user,'EMP');
PL/SQL procedure successfully completed.

--再次查看emp表的基本信息：
suxing@PROD>SELECT num_rows, blocks, empty_blocks as empty, avg_space, chain_cnt, avg_row_len
2 FROM user_tables where table_name = 'EMP';
NUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ---------- -----------
3500 622 0 0 0 69

suxing@PROD>select segment_name,tablespace_name,bytes,blocks
2 from user_segments
3 where segment_name='EMP';

SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS
-------------------- ------------------------------ ---------- ----------
EMP USERS 5242880 640

--尝试直接shrink表回收高水位：
suxing@PROD>alter table emp shrink space;
alter table emp shrink space
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled
#发现配置不足。

--启动行移动：
suxing@PROD>alter table emp enable row movement;
Table altered.
#开启行移动。
suxing@PROD>alter table emp shrink space;
Table altered.

--查看索引的状态：
suxing@PROD>select INDEX_NAME,STATUS from user_indexes where INDEX_NAME=upper('idx_emp_salary');
INDEX_NAME STATUS
------------------------------ --------
IDX_EMP_SALARY VALID
#索引没有失效。

--查看表的基本信息：
suxing@PROD>SELECT num_rows, blocks, empty_blocks as empty, avg_space, chain_cnt, avg_row_len
2 FROM user_tables where table_name = 'EMP';

NUM_ROWS BLOCKS EMPTY AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ---------- ---------- ---------- -----------
3500 622 0 0 0 69

suxing@PROD>select segment_name,tablespace_name,bytes,blocks
2 from user_segments
3 where segment_name='EMP';

SEGMENT_NAME TABLESPACE_NAME BYTES BLOCKS
-------------------- ------------------------------ ---------- ----------
EMP USERS 393216 48
#表包含的数据块数发现了明显的变化。

小结：
1,alter table shrink space compact cascade;对大表或大索引操作会产生大量日志
2,alter table shrink space;--加上compact选项仅重新整理segment 空间，并压缩表的记录在以后进行release空间.但数据库并不调整hwm及释放空间.为了释放空间.你必须再发布alter table shrink space
--compact用于把一个长操作分割为两个较短的操作
--须开启行移动
--cascade会级联对其依赖对象（比如上面的索引）进行压紧操作
3,它的一些限制条件：
在集群表，long列的表不能采用shrink 操作
在基于函数索引或位图联接索引不支持shrink操作
就是你指定了cascade选项，也不能shrink 索引组织表的映射表
压缩表不能用shrink操作
构建了on commit物化视图的主表，在shrink操作后，rowid实化视图必须重建
4,alter table shrink space;--仅可对automatic segment management tablespace方式的table,index_orginized table or overflow segment,index,partition,lob segment,mv,mv log
--压缩segment,调整hwm,并马上释放空间

ITPub博客

回收高水位线

最新文章