Admin Segment Space Management Auto
create table test01 as
select * from dba_objects where 1=0
/
analyze table scott.test01 compute statistics
/
select * from dba_tables
where owner = 'SCOTT' and table_name = 'TEST01'
/
initial_extent-->65536bytes-->64k-->8blocks
blocks-->0
empty_blocks-->8
insert into test01
select * from dba_objects
/
commit
/
analyze table scott.test01 compute statistics
/
select * from dba_tables
where owner = 'SCOTT' and table_name = 'TEST01'
/
num_rows-->42482
blocks-->1252
empty_blocks-->28
alter table test01 allocate extent (size 1m)
/
commit
/
analyze table scott.test01 compute statistics
/
select * from dba_tables
where owner = 'SCOTT' and table_name = 'TEST01'
/
num_rows-->42482
blocks-->1252
empty_blocks-->156 #156-28=128*8k=1M
alter table scott.test01 deallocate unused
/
analyze table scott.test01 compute statistics
/
select * from dba_tables
where owner = 'SCOTT' and table_name = 'TEST01'
/
num_rows-->42482
blocks-->1252
empty_blocks-->28
create table test02
storage (initial 1m)
as
select * from test01
/
analyze table scott.test02 compute statistics
/
select * from dba_tables
where owner = 'SCOTT' and table_name in ('TEST01','TEST02')
/
test01
num_rows-->4966
blocks-->1756
empty_blocks-->36

test02
num_rows-->4966
blocks-->74
empty_blocks-->54
alter table test01 enable row  movement
/
alter table scott.test01 shrink space compact
/
analyze table scott.test01 compute statistics
/
select * from dba_tables
where owner = 'SCOTT' and table_name in ('TEST01','TEST02')
/
test01
num_rows-->4966
blocks-->66
empty_blocks-->14

test02
num_rows-->4966
blocks-->74
empty_blocks-->54
Unless otherwise stated, the content of this page is licensed under Creative Commons Attribution-ShareAlike 3.0 License