Move会移动高水位,但不会释放申请的空间,是在高水位以下(below HWM)的操作。(本人觉得这种说法不准确,而应该是最多收缩到initial分配的大小) 但是在本人的试验中,(版本10.2.0.1.0): 删除部分数据:
================================================ 9i的情况:(9i不支持shrink)
而shrink space 同样会移动高水位,但也会释放申请的空间,是在高水位上下(below and above HWM)都有的操作。 SQL> select * from v$version; BANNER Table created. SQL> analyze table test compute statistics; Table analyzed. SQL> col SEGMENT_NAME for a10 SEGMENT_NA EXTENTS BLOCKS INIT SQL> col TABLE_NAME for a10 TABLE_NAME BLOCKS EMPTY_BLOCKS SQL> begin PL/SQL procedure successfully completed. SQL> analyze table test compute statistics; Table analyzed. SQL> select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME='TEST'; SEGMENT_NA EXTENTS BLOCKS SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST'; TABLE_NAME BLOCKS EMPTY_BLOCKS SQL> delete from test where rownum<=50000; 50000 rows deleted. SQL> analyze table test compute statistics; Table analyzed. SQL> select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME='TEST'; SEGMENT_NA EXTENTS BLOCKS SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST'; TABLE_NAME BLOCKS EMPTY_BLOCKS SQL> select count(distinct dbms_rowid.rowid_block_number(rowid)) used_blocks from test; USED_BLOCKS SQL> alter table test move; Table altered. SQL> analyze table test compute statistics; Table analyzed. SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST'; TABLE_NAME BLOCKS EMPTY_BLOCKS SQL> select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME='TEST'; SEGMENT_NA EXTENTS BLOCKS SQL> alter table test enable row movement; Table altered. SQL> alter table test shrink space; Table altered. SQL> analyze table test compute statistics; Table analyzed. SQL> select SEGMENT_NAME,EXTENTS,BLOCKS from user_segments where SEGMENT_NAME='TEST'; SEGMENT_NA EXTENTS BLOCKS SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST'; TABLE_NAME BLOCKS EMPTY_BLOCKS
注意: SQL> drop table test; Table dropped. SQL> create table test (id number) storage (initial 10m next 1m) tablespace users; Table created. SQL> analyze table test compute statistics; Table analyzed. SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGME SEGMENT_NA EXTENTS BLOCKS INIT SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST'; TABLE_NAME BLOCKS EMPTY_BLOCKS SQL> alter table test move storage (initial 1m); Table altered. SQL> analyze table test compute statistics; Table analyzed. SQL> select SEGMENT_NAME,EXTENTS,BLOCKS,INITIAL_EXTENT/1024/1024 init from user_segments where SEGME SEGMENT_NA EXTENTS BLOCKS INIT SQL> select TABLE_NAME,BLOCKS,EMPTY_BLOCKS from user_tables where table_name='TEST'; TABLE_NAME BLOCKS EMPTY_BLOCKS 2.使用move时,会改变一些记录的ROWID,所以MOVE之后索引会变为无效,需要REBUILD。 |
转载于:http://blog.itpub.net/7353848/viewspace-695741/