Shrink

dba_scheduler_job에 auto_space_advisor_job이 등록되어 있어야함 --tuning pack

select * from table(dbms_space.asa_recommendations());

-- shrink 가능여부
select dt.owner, dt.table_name, (case when nvl(ind.cnt, 0) < 1 then 'Y' else 'N' end) as shrinkable
from dba_tables dt, (select table_name, count(*) cnt
from dba_indexes di
where index_type like '%FUNCTION-BASED%'
group by table_name ) ind
where dt.table_name = ind.table_name(+)
and dt.table_name not like 'AQ$%'
and dt.table_name not like 'BIN$%'
--and dt.owner=''
order by 1, 2 ;

-- 1.해당테이블 조회
select owner,segment_name, segment_type, bytes/1024/1024 as MB from dba_segments where segment_name='<segment_name>';

 

-- 2. row movement enable
alter table <owner>.<table_name> enable row movement;

 

-- 3. shrink 진행
alter table <owner>.<table_name> shrink space compact; -- Block 만 정리
alter table <owner>.<table_name> shrink space;             -- Block, HWM 포함
alter table <owner>.<table_name> shrink space cascade;  -- 관련된 object 전부

 

-- 4. row movement disable
alter table <owner>.<table_name> disable row movement;

 

-- 5. 유의사항
LONG열을 포함하지 않은 것
클러스터 테이블이 아닌 것
압축테이블 아닌 것
Function Index, bitmap index
ROWID가 변경되므로 M-View는 재 구축해줘야함