Oracle Segment Advisor 10g.

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       :