free unused space under HWM.

prerequisites: enable table row movement.

1, shrink space.

ALTER TABLE T2 SHRINK SPACE ;

2, move [tablespace].

ALTER TABLE T2 MOVE ;

-- sample code:

SELECT BLOCKS FROM USER_SEGMENTS WHERE SEGMENT_NAME='T2' ;
--384, 512
ALTER TABLE T2 ALLOCATE EXTENT (SIZE 1M) ;
exec dbms_stats.gather_table_stats(user,'t2')
DELETE T2 WHERE DEPTNO=10 ;
ALTER TABLE T2 DEALLOCATE UNUSED ;
ALTER TABLE T2 ENABLE ROW MOVEMENT;

-- blocks in user_tables indicate the HWM block.

select TABLE_NAME, BLOCKS,NUM_ROWS, A.sample_size, A.empty_blocks from user_tables A where table_name='T2' ;

[@more@]

ITPub博客

HWM 释放空闲空间

最新文章