How to defragment / shrink table and indexes in Oracle?

Post Reply
Admin
Site Admin
Posts: 69
Joined: Thu Sep 24, 2020 5:40 pm

How to defragment / shrink table and indexes in Oracle?

Post by Admin »

How to defragment / shrink table and indexes in Oracle?

Code: Select all


sqlplus -s ...

set lines 1200
set pages 100

select segment_name, sum(bytes)/1024/1024/1024 "Size in GB"
from dba_segments
where table_name in ('<table_name>')
group by segment_name
order by 2 desc;

select segment_name, sum(bytes)/1024/1024/1024 "Size in GB"
from dba_segments
where segment_name in (select index_name from dba_indexes where table_name = '<table_name>')
group by segment_name
order by 2 desc;

select 'start time:'||to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;

alter session enable parallel ddl;

alter session set parallel_force_local=TRUE;

alter session force parallel query parallel 10;

alter table <table_name> enable row movement;

alter table <table_name> shrink space;

alter table <table_name> disable row movement;

alter index <index_name> rebuild online parallel 10;

Post Reply