Segment Advisor related SQL

/*
create table big_table (id number, name char(200));
insert into big_table select rownum,'a' from dual connect by rownum<100000;
delete from big_table where mod(id,10)<>0;
commit;
*/

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(ALL_RUNS=>'FALSE', SHOW_MANUAL=>'FALSE',SHOW_FINDINGS=>'FALSE'));



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;




DECLARE
 my_task_id NUMBER;
 obj_id NUMBER;
 my_task_name VARCHAR2(100);
 my_task_desc VARCHAR2(500);
BEGIN           
 my_task_name := 'Adhoc Segment Advice for table Donghua.BIG_TABLE';
 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 => 'TABLE',
                              attr1 => 'DONGHUA',
                              attr2 => 'BIG_TABLE',
                              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;
/


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(ALL_RUNS=>'TRUE', SHOW_MANUAL=>'TRUE',SHOW_FINDINGS=>'FALSE'));





-- 10gR2 view
-- DBA_AUTO_SEGADV_SUMMARY

select * from dictionary where table_name like '%AUTO%';

select job_start_time,job_status,job_duration,job_error
from DBA_AUTOTASK_JOB_HISTORY
where client_name='auto space advisor'
order by job_start_time;

select * from dba_autotask_client;



select * from DBA_HIST_SEG_STAT;