Automated Segment Advisor Advice

Run This Query then after check the output and take the action of the object--

Detect (Row spaning multiple blocks i.e row chaining ,Reclaiming space) with advice

-----Through Package

DESC DBMS_SPACE

SELECT
'SEGMENT ADVICE--------------------------------------------------'  ||chr(10)||
'SEGMENT OWNER         : '||            SEGMENT_OWNER                  ||chr(10)||
'SEGMENT NAME            : '||            SEGMENT_NAME                     ||chr(10)||
'SEGMENT TYPE              : '||            SEGMENT_TYPE                       ||chr(10)||
'TABLESPACE NAME      : '||            TABLESPACE_NAME               ||chr(10)||
'ALLOCATED SPACE       : '||            ALLOCATED_SPACE               ||chr(10)||
'RECLAIMABLE SPACE   : '||            RECLAIMABLE_SPACE           ||chr(10)||
'RECOMMENDATIONS   : '||            RECOMMENDATIONS             ||chr(10)||
'SOLUTION 1                    : '||            C1                                                ||chr(10)||
'SOLUTION 2                    : '||            C2                                                ||chr(10)||
'SOLUTION 3                    : '||            C3 ADVICE
FROM table(dbms_space.asa_recommendations('FALSE','FALSE','FALSE'))
/

Reclaimed space more than 20 MB




---Through Data Dictionary (3 Views)


SELECT * FROM DBA_ADVISOR_EXECUTIONS //11g
OR
SELECT * FROM DBA_ADVISOR_LOG // 10g

SELECT * FROM DBA_ADVISOR_FINDINGS
SELECT * FROM DBA_ADVISOR_OBJECTS




SELECT 'TASK_NAME     :'||f.TASK_NAME                                           ||chr(10)||
'start run time                        :'||to_char(execution_start,'dd-mon-yy hh12: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_LOG l
WHERE o.TASK_ID=f.TASK_ID
AND o.OBJECT_ID=f.OBJECT_ID
and f.task_id=l.task_id
and l.execution_start > sysdate - 1
and o.type in ('TABLE','INDEX')
order by f.task_name

/


## Re-organize object:


Shrink Space


alter table <table_name> enable row movement;

Note:- Mannualy,Recommend to use shrink space because after that shrink table reset the HWM they have to work of defragmentation 'i.e' requried row movement 
alter table <table_name> shrink space; //only table segment
alter table <table_name> shrink_space cascade; //table & index sgement



Shrink Space Compact
Note:- No Re-commend not defragmentation  Recover space, but don't amend the high water mark (HWM)
alter table <table_name> shrink space compact;


De-allocate Unused Space

It's Explicitly ,Deallocates unused space beginning from the end of the objects (allocated space) and moving downwards
toward the beginning of the object, continuing down until it reaches the high water mark (HWM).For indexes, "deallocate unused space"
coalesces all leaf blocks within same branch of b-tree, and quickly frees up index leaf blocks for use.

deallocate - simply deallocates unused space at the end of the segment; doesn't move any data

alter table <.......> deallocate unused space;
alter index <......> deallocate unused space;


Shrink Space Restriction
· You cannot specify this clause for a cluster, a clustered table, or any object with a LONG column.
· Segment shrink is not supported for tables with function-based indexes or bitmap join indexes. 
· This clause does not shrink mapping tables of index-organized tables, even if you specify CASCADE.
· You cannot specify this clause for a compressed table.
· You cannot shrink a table that is the master table of an ON COMMIT materialized view. Rowid materialized views must be rebuilt after the shrink operation.
· Segment shrink is not supported for tables with Domain indexes.