alter table ~~~ shrink space

1. 기본 환경 설정

create tablespace d_test
datafile '....../d_test01.dbf' size 10m
extent management local uniform size 512K
segment space management auto;

create tablespace i_test
datafile '....../i_test01.dbf' size 10m
extent management local uniform size 512K
segment space management auto;

create user test identified by test
default tablespace d_test;

grant connect, resource to test;

connect test/test

create table test_object as
select * from all_objects;

insert into test_object select * from test_object;

/

/

/

/

/

commit;

select count(*) from test_object;

  COUNT(*)
----------
     70208

select count(distinct dbms_rowid.rowid_block_number(rowid)) blocks
from   test_object;

    BLOCKS
----------
       873

    BLOCKS    EXTENTS
---------- ----------
      1216         19

select blocks, extents
from   user_segments
where  segment_name = 'TEST_OBJECT';

delete from TEST_OBJECT
where  owner = 'WMSYS';

delete from test_object where owner = 'PUBLIC';

commit;

select count(distinct dbms_rowid.rowid_block_number(rowid)) blocks
from   test_object;

    BLOCKS
----------
       861

alter table test_object shrink space compact;
alter table test_object shrink space compact
*
1행에 오류:
ORA-10636: ROW MOVEMENT is not enabled

alter table test_object enable row movement;

alter table test_object shrink space compact;

select count(distinct dbms_rowid.rowid_block_number(rowid)) blocks
from   test_object;

    BLOCKS
----------
       323 

select blocks, extents from user_segments
where  segment_name = 'TEST_OBJECT';

    BLOCKS    EXTENTS
---------- ----------
      1216         19

alter table test_object shrink space;

select blocks, extents
from   user_segments
where  segment_name = 'TEST_OBJECT';

    BLOCKS    EXTENTS
---------- ----------
       384          6