Segmet Advisor em bloco PL/SQL

Olá,

Abaixo segue um exemplo do uso do Segment Advisor dentro do bloco PL/SQL e ao final as views que devem ser consultadas para recuperar as recomendações.


--Inicio do bloco pl/sql

variable id number ;

BEGIN

DECLARE

name_task varchar2(100);
desc_task varchar2(500);
obj_id number ;

begin

execute immediate 'alter table cmt.userdocuments enable row movement';


--name_task := 'Segment Advisor example on userdocuments' ;
name_task :=''; --vai ser gerado pelo creaste task
desc_task := 'Teste do uso de segment advisor na tabela userdocuments';

dbms_advisor.create_task(
advisor_name => 'Segment Advisor',
task_id => :id,
task_name => name_task,
task_desc => desc_task );

/*dbms_advisor.create_object(
task_name => name_task,
object_type =>'TABLE',
attr1 => 'CMT',
attr2 => 'USERDOCUMENTS',
-- attr3 => null,
-- attr4 => null,
-- attr5 => null,
obj_id => obj_id) ;*/

dbms_advisor.create_object(name_task,'TABLE','CMT','USERDOCUMENTS',NULL,NULL,obj_id) ;

dbms_advisor.set_task_parameter(
task_name => name_task,
parameter => 'recommend_all',
value => 'TRUE');

dbms_advisor.execute_task (name_task) ;

end;

END;

select owner,task_id,task_name,type,message,more_info from dba_advisor_findings
where task_id = 28198

select owner,task_id,task_name,benefit_type
from dba_advisor_recommendations
where task_id = 28198

select owner,task_id,task_name,command,attr1
from dba_advisor_actions
where task_id = 28198