Comparision between shrink space, shrink space compact and shrink space cascade

drop table big_table purge;
create table big_table (id number(18,0), name char(1000));
create index big_table_n1 on big_table(name);
create unique index big_table_pk on big_table(id);
alter table big_table add constraint big_table_pk primary key(id) using index big_table_pk;
insert into big_table select rownum, dbms_random.string('P',10) from dual connect by rownum < 10000;
delete from big_table where mod(id,10)<>0;
commit;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter table big_table enable row movement;
alter table big_table shrink space compact;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
/****************************************************************************************************
table BIG_TABLE dropped.
table BIG_TABLE created.
index BIG_TABLE_N1 created.
unique index BIG_TABLE_PK created.
table BIG_TABLE altered.
9,999 rows inserted.
9,000 rows deleted.
commited.
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 19922944
BIG_TABLE 12582912

table BIG_TABLE altered.
table BIG_TABLE altered.
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 19922944
BIG_TABLE 12582912
****************************************************************************************************/
drop table big_table purge;
create table big_table (id number(18,0), name char(1000));
create index big_table_n1 on big_table(name);
create unique index big_table_pk on big_table(id);
alter table big_table add constraint big_table_pk primary key(id) using index big_table_pk;
insert into big_table select rownum, dbms_random.string('P',10) from dual connect by rownum < 10000;
delete from big_table where mod(id,10)<>0;
commit;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter table big_table enable row movement;
alter table big_table shrink space;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter index big_table_n1 shrink space compact; -- same as 'alter index big_table_pk coalesce'
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter index big_table_n1 coalesce;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter index big_table_n1 shrink space;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter index big_table_n1 rebuild online;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter index big_table_n1 rebuild;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';

/****************************************************************************************************
table BIG_TABLE dropped.
table BIG_TABLE created.
index BIG_TABLE_N1 created.
unique index BIG_TABLE_PK created.
table BIG_TABLE altered.
9,999 rows inserted.
9,000 rows deleted.
commited.
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 19922944
BIG_TABLE 12582912

table BIG_TABLE altered.
table BIG_TABLE altered.
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 19922944
BIG_TABLE 1310720

index BIG_TABLE_N1 altered. //alter index big_table_n1 shrink space compact
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 19922944
BIG_TABLE 1310720

index BIG_TABLE_N1 altered. //alter index big_table_n1 coalesce;
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 19922944
BIG_TABLE 1310720

index BIG_TABLE_N1 altered. //alter index big_table_n1 shrink space;
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 1376256
BIG_TABLE 1310720

index BIG_TABLE_N1 altered. //alter index big_table_n1 rebuild online;
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 2097152
BIG_TABLE 1310720

index BIG_TABLE_N1 altered. //alter index big_table_n1 rebuild;
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 2097152
BIG_TABLE 1310720
****************************************************************************************************/
drop table big_table purge;
create table big_table (id number(18,0), name char(1000));
create index big_table_n1 on big_table(name);
create unique index big_table_pk on big_table(id);
alter table big_table add constraint big_table_pk primary key(id) using index big_table_pk;
insert into big_table select rownum, dbms_random.string('P',10) from dual connect by rownum < 10000;
delete from big_table where mod(id,10)<>0;
commit;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
alter table big_table enable row movement;
alter table big_table shrink space cascade;
select segment_name,bytes from dba_segments where owner='DONGHUA' and segment_name like 'BIG_TABLE%';
/****************************************************************************************************
table BIG_TABLE dropped.
table BIG_TABLE created.
index BIG_TABLE_N1 created.
unique index BIG_TABLE_PK created.
table BIG_TABLE altered.
9,999 rows inserted.
9,000 rows deleted.
commited.
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 196608
BIG_TABLE_N1 19922944
BIG_TABLE 12582912

table BIG_TABLE altered.
table BIG_TABLE altered.
SEGMENT_NAME BYTES
--------------------------------------------------------------------------------- -----
BIG_TABLE_PK 65536
BIG_TABLE_N1 1376256
BIG_TABLE 1310720
****************************************************************************************************/