Segment advisor which is available from oracle 10g onwards helps
in detecting segments that have space issues.

For example if a table had lots of rows earlier but now it has few
rows then it may be hording up space because of its high water mark.

Segment advisor helps in finding following
types of segments.

a) Segments that are good candidates for shrink operations.
b) Segments that have significant row chaining.
c) Segments that may benefit in OLTP compression(11g).

Although segment advisor runs automatically in oracle database
at least once a day but if you want to run it manually then
execute the following command.


DECLARE
my_task_id number;
obj_id number;
my_task_name varchar2(100);
my_task_desc varchar2(500);
BEGIN
my_task_name := 'Advice';
my_task_desc := 'Manual Segment Advisor Run';
---------
-- Step 1
---------
dbms_advisor.create_task (
advisor_name => 'Segment Advisor',
task_id => my_task_id,
task_name => my_task_name,
task_desc => my_task_desc);
---------
-- Step 2
---------
dbms_advisor.create_object (
task_name => my_task_name,
object_type => 'TABLESPACE',
attr1 => 'USERS',
attr2 => NULL,
attr3 => NULL,
attr4 => NULL,
attr5 => NULL,
object_id => obj_id);
---------
-- Step 3
---------
dbms_advisor.set_task_parameter(
task_name => my_task_name,
parameter => 'recommend_all',
value => 'TRUE');
---------
-- Step 4
---------
dbms_advisor.execute_task(my_task_name);
END;
/


In the dbms_advisor.create_object procedure i am running
the segment advisor on the tablespace users.

Then you can execute the following command to get recommendations.


SELECT
'Segment Advice --------------------------'|| chr(10) ||
'TABLESPACE_NAME : ' || tablespace_name || chr(10) ||
'SEGMENT_OWNER : ' || segment_owner || chr(10) ||
'SEGMENT_NAME : ' || segment_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'));



Here is a sample output.

Segment Advice --------------------------
TABLESPACE_NAME : USERS
SEGMENT_OWNER : HR
SEGMENT_NAME : EMPLOYEES
ALLOCATED_SPACE : 20971520
RECLAIMABLE_SPACE: 18209960
RECOMMENDATIONS : Perform re-org on the object EMPLOYEES,
estimated savings is 18209960 bytes.
SOLUTION 1 : Perform Reorg
SOLUTION 2 :
SOLUTION 3 :