ALTER TABLE MOVE | SHRINK SPACE區別

guocun09發表於2012-01-04

ALTER TABLE MOVE | SHRINK SPACE區別

1.move

analyze table GC.R_WIP_KEYPARTS_T compute statistics;

 

SELECT BLOCKS,EMPTY_BLOCKS FROM DBA_TABLES

WHERE OWNER='GC' AND TABLE_NAME='R_WIP_KEYPARTS_T';

BLOCKS:137452

EMPTY_BLOCKS:1812

 

SELECT BLOCKS FROM DBA_SEGMENTS

WHERE SEGMENT_NAME='R_WIP_KEYPARTS_T';

BLOCKS:139264

 

DELETE from GC.R_WIP_KEYPARTS_T where rownum<5000;

這是資料被刪除,但HWM不會下降。

 

select count(distinct dbms_rowid.rowid_block_number(rowid)) from GC.R_WIP_KEYPARTS_T;

可以看到當前行所佔的block數。肯定比BLOCKS:137452

 

ALTER TABLE GC.R_WIP_KEYPARTS_T  MOVE

 

analyze table GC.R_WIP_KEYPARTS_T compute statistics;

 

SELECT BLOCKS,EMPTY_BLOCKS FROM DBA_TABLES

WHERE OWNER='GC' AND TABLE_NAME='R_WIP_KEYPARTS_T';

BLOCKS:137381 --move之後看到BLOCKS減少

EMPTY_BLOCKS:1883

 

SELECT BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='R_WIP_KEYPARTS_T';

BLOCKS:139264 --空間並沒有回收,總的塊數還是139264,但HWM下降到了137381

注:DBA_TABLES中欄位含義

BLOCKS*

Number of used data blocks in the table

EMPTY_BLOCKS*

Number of empty (never used) data blocks in the table

 

 

 

2.SHRINK SPACE

ALTER TABLE GC.R_WIP_keyparts_T SHRINK SPACE;

 

ANALYZE TABLE GC.R_WIP_KEYPARTS_T COMPUTE STATISTICS;

 

SELECT BLOCKS FROM DBA_SEGMENTS WHERE SEGMENT_NAME='R_WIP_KEYPARTS_T';

BLOCKS:137312

 

SELECT BLOCKS,EMPTY_BLOCKS FROM DBA_tableS WHERE table_NAME='R_WIP_KEYPARTS_T';

BLOCKS:136688   EMPTY_BLOCKS:624

可以看到除了HWM下的空間下降,HWM上未使用的空塊也會有回收

 

 

 

 

 

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25583515/viewspace-714402/,如需轉載,請註明出處,否則將追究法律責任。

相關文章