begin
my_task_name :='advisor_test tab 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=>'TABLE',
attr1=>'DBMON',
attr2 => 'ADVISOR_TEST',
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;
/ 已执行
SQL> select
2 /* "|chr(13)||chr(10)"为windows平台的换行符，如果是linux等其它平台，请用"chr(10)"代替 */
3 'Task name :'||f.task_name||chr(13)||chr(10)||
4 'Segment name :'||o.attr2 ||chr(13)||chr(10)||
5 'Sement type :'||o. type ||chr(13)||chr(10)||
6 'partition name:'||o.attr3 ||chr(13)||chr(10)||
7 'Message :'||f.message ||chr(13)||chr(10)||
8 'More info :'||f.more_info TASK_ADVICE
9 from dba_advisor_findings f,dba_advisor_objects o
10 where o.task_id=f.task_id
11 and o.object_id=f.object_id
12 and f.task_name = 'advisor_test tab Advice'
13 order by f.task_name;
TASK_ADVICE
--------------------------------------------------------------------------------
Task name :advisor_test tab Advice
Segment name :ADVISOR_TEST
Sement type : TABLE
partition name:
Message :启用表 DBMON.ADVISOR_TEST 的行移动并执行收缩, 估计可以节省 285435
31 字节。
More info :分配空间:75497472: 已用空间:46953941: 可回收空间:28543531:
八、案例2（表空间级别运行） declare
my_task_id number;
obj_id number;
my_task_name varchar2(100);
my_task_desc varchar2(500);
begin
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;
/
SQL> select
2 /* "|chr(13)||chr(10)"为windows平台的换行符，如果是linux等其它平台，请用"chr(10)"代替 */
3 'Task name :'||f.task_name||chr(13)||chr(10)||
4 'Segment name :'||o.attr2 ||chr(13)||chr(10)||
5 'Sement type :'||o. type ||chr(13)||chr(10)||
6 'partition name:'||o.attr3 ||chr(13)||chr(10)||
7 'Message :'||f.message ||chr(13)||chr(10)||
8 'More info :'||f.more_info TASK_ADVICE
9 from dba_advisor_findings f,dba_advisor_objects o
10 where o.task_id=f.task_id
11 and o.object_id=f.object_id
12 and f.task_name = 'Tablespace Advice'
13 order by f.task_name;
TASK_ADVICE
--------------------------------------------------------------------------------
Task name :Tablespace Advice
Segment name :T_SCEGAOKAOQUERY_GZ
Sement type : TABLE
partition name:
Message :此对象中的空闲空间小于 10MB。
More info :分配空间:65536: 已用空间:8192: 可回收空间:57344:
Task name :Tablespace Advice
Segment name :T_SCEGAOKAOQUERY_GZ
Sement type : TABLE
partition name:
TASK_ADVICE
--------