Reclaiming Unused Space in Index.

We can see Reclaimable space in Schema by using below query.

SELECT'Task Name : ' || f.task_name || CHR(10) ||'Start Run Time : ' || TO_CHAR(execution_start, 'dd-mon-yy hh24:mi') || chr (10) ||'Segment Name : ' || o.attr2 || CHR(10) ||'Segment Type : ' || o.type || CHR(10) ||'Partition Name : ' || o.attr3 || CHR(10) ||'Message : ' || f.message || CHR(10) ||'More Info : ' || f.more_info || CHR(10) ||
'------------------------------------------------------' Advice FROM dba_advisor_findings f,dba_advisor_objects o,dba_advisor_executions e WHERE o.task_id = f.task_id AND o.object_id = f.object_id AND f.task_id = e.task_id AND e. execution_start > sysdate - 1 AND e.advisor_name = 'Segment Advisor' ORDER BY f.task_name;

Solution

There are a couple of effective methods for freeing up unused space associated with an index:




•Rebuilding the index
•Shrinking the index


Before you perform either of these operations, first check
USER_SEGMENTS to verify that the amount of space used corresponds with the Segment Advisor’s advice. In this example, the segment name is F_REGS_IDX1

SQL> select bytes from user_segments where segment_name = 'F_REGS_IDX1';
BYTES----------
166723584

This example uses the
ALTER INDEX...REBUILD

statement to re-organize and compact the space usedby an index:

SQL> alter index f_regs_idx1 rebuild;
Alternatively, use the
ALTER INDEX...SHRINK SPACE statement to free up unused space in an index—for example:

SQL> alter index f_regs_idx1 shrink space;

Now query

USER_SEGMENTS

again to verify that the space has been de-allocated. Here is the output forthis example:

BYTES----------
524288

The space consumed by the index has considerably decreased.

I prefer export and import is the another useful method to reclaim free space.

Read More