segmentadvisor.txt

在oracle中

--创建目录WORK_DIR
create directory TUNE_DIR as 'E:\oracle\product\10.2.0\Tune';

--替换任务名称SEGMENTADV_903575


--分析表空间
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_903575';
taskdesc :='根据对象增长趋势获取收缩建议';
numDaysToRetain :='30';
dbms_advisor.create_task('Segment Advisor',?,taskname,taskdesc,NULL);
dbms_advisor.create_object(taskname, 'TABLESPACE', 'EXAMPLE', ' ', ' ', NULL, object_id);
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_903575';
dbms_advisor.reset_task(taskname);
dbms_advisor.execute_task(taskname);
dbms_advisor.create_file(dbms_advisor.get_task_script(taskname),'TUNE_DIR','tablespace_tune.txt');
END;



--分析对象
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 := 'liuhz_table_analyze';
taskdesc :='根据对象增长趋势获取收缩建议';
numDaysToRetain :='30';
dbms_advisor.create_task('Segment Advisor',?,taskname,taskdesc,NULL);
dbms_advisor.create_object(taskname, 'TABLE', 'SOSV3', 'USER_INFO', ' ', 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 := 'liuhz_table_analyze';
dbms_advisor.reset_task(taskname);
dbms_advisor.execute_task(taskname);
dbms_advisor.create_file(dbms_advisor.get_task_script(taskname),'TUNE_DIR','object_tune.txt');
END;