segment advisory

[steeve123]

i dont have the dbconsole installed on my server. Will install sometimes next month.

DECLARE

taskname varchar2(100);
taskdesc varchar2(128);
task_id number;
object_id number;
timeLimit varchar2(25);
numDaysToRetain varchar2(25);
objectName varchar2(100);
objectType varchar2(100);

BEGIN
taskname := 'SEGMENTADV_7848148';
taskdesc :='Get shrink advice based on object growth trend';
numDaysToRetain :='30';
dbms_advisor.create_task('Segment Advisor',?,taskname,taskdesc,NULL);
dbms_advisor.create_object(taskname, 'TABLESPACE', 'USERS', ' ', ' ', NULL, object_id);
dbms_advisor.set_task_parameter(taskname, 'RECOMMEND_ALL', 'TRUE');
dbms_advisor.set_task_parameter(taskname, 'DAYS_TO_EXPIRE', numDaysToRetain);
END;

DECLARE
taskname varchar2(100);
BEGIN
taskname := 'SEGMENTADV_7848148';
dbms_advisor.reset_task(taskname);
dbms_advisor.execute_task(taskname);
END;


Question:

How do i query wich object need shrink after i ran my advisor?

[Thomasps]

select af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message 
  from dba_advisor_findings af, dba_advisor_objects ao
  where ao.task_id = af.task_id
  and ao.object_id = af.object_id ;

Also, include your TASK_ID in WHERE clause.

You can also use DBMAS_SPACE.ASA_RECOMMENDATIONS function to extract the results.

To read more...
http://download-east.oracle.com/docs...231/schema.htm