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.
