1. High water mark 까지 할당된 block 의 갯수 확인
select segment_name, blocks
from dba_segments
where owner = 'SH'
and segment_type = 'TABLE'
order by blocks desc;
2. 테이블 compact 작업 수행
select 'alter table ' || table_name || ' enable row movement;'
from all_tables
where owner='SH'
union all
select 'alter table ' || table_name || ' shrink space compact;'
from all_tables
where owner='SH'
union all
select 'alter table ' || table_name || ' shrink space;'
from all_tables
where owner='SH';
alter table SALES_TRANSACTIONS_EXT enable row movement;
alter table TIMES enable row movement;
alter table PRODUCTS enable row movement;
alter table CHANNELS enable row movement;
alter table PROMOTIONS enable row movement;
alter table CUSTOMERS enable row movement;
alter table SUPPLEMENTARY_DEMOGRAPHICS enable row movement;
alter table CAL_MONTH_SALES_MV enable row movement;
alter table FWEEK_PSCAT_SALES_MV enable row movement;
alter table SALES enable row movement;
alter table COSTS enable row movement;
alter table SALES_TRANSACTIONS_EXT shrink space compact;
alter table TIMES shrink space compact;
alter table PRODUCTS shrink space compact;
alter table CHANNELS shrink space compact;
alter table PROMOTIONS shrink space compact;
alter table CUSTOMERS shrink space compact;
alter table SUPPLEMENTARY_DEMOGRAPHICS shrink space compact;
alter table CAL_MONTH_SALES_MV shrink space compact;
alter table FWEEK_PSCAT_SALES_MV shrink space compact;
alter table SALES shrink space compact;
alter table COSTS shrink space compact;
3. High water mark 를 내려주는 작업 수행
alter table SALES_TRANSACTIONS_EXT shrink space;
alter table TIMES shrink space;
alter table PRODUCTS shrink space;
alter table CHANNELS shrink space;
alter table PROMOTIONS shrink space;
alter table CUSTOMERS shrink space;
alter table SUPPLEMENTARY_DEMOGRAPHICS shrink space;
alter table CAL_MONTH_SALES_MV shrink space;
alter table FWEEK_PSCAT_SALES_MV shrink space;
alter table SALES shrink space;
alter table COSTS shrink space;
4. INDEX 결과