table partition name
Unused. Specify NULL.
INDEX PARTITION
schema name
index name
index partition name
Unused. Specify NULL.
TABLE SUBPARTITION
schema name
table name
table subpartition name
Unused. Specify NULL.
INDEX SUBPARTITION
schema name
index name
index subpartition name
Unused. Specify NULL.
LOB
schema name
segment name
NULL
Unused. Specify NULL.
LOB PARTITION
schema name
segment name
lob partition name
Unused. Specify NULL.
LOB SUBPARTITION
schema name
segment name
lob subpartition name
Unused. Specify NULL.
Table 14-4 Input forDBMS_ADVISOR.SET_TASK_PARAMETER
| Input Parameter |
Description |
Possible Values |
Default Value |
| time_limit |
The time limit for the Segment Advisor run, specified in seconds. |
Any number of seconds |
UNLIMITED |
| recommend_all |
Whether the Segment Advisor should generate findings for all segments. |
TRUE: Findings are generated on all segments specified, whether or not space reclamation is recommended. FALSE: Findings are generated only for those objects that generate recommendations for space reclamation. |
TRUE |
Example:
variable id number;
begin
declare
name varchar2(100);
descr varchar2(500);
obj_id number;
begin
name:='Manual_Employees';
descr:='Segment Advisor Example';
dbms_advisor.create_task (
advisor_name => 'Segment Advisor',
task_id => :id,
task_name => name,
task_desc => descr);
dbms_advisor.create_object (
task_name => name,
object_type => 'TABLE',
attr1 => 'HR',
attr2 => 'EMPLOYEES',
attr3 => NULL,
attr4 => NULL,
attr5 => NULL,
object_id => obj_id);
dbms_advisor.set_task_parameter(
task_name => name,
parameter => 'recommend_all',
value => 'TRUE');
dbms_advisor.execute_task(name);
end;
end;
/
查看segmentadvisor结果
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('TRUE','TRUE', 'FALSE'));
或者：
SELECT
'Task Name : ' || f.task_name || 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 TASK_ADVICE
FROMdba_advisor_findings f
,dba_advisor_objects o
WHERE o.task_id =f.task_id
AND o.object_id =f.object_id
AND f.task_name like'BIG_TABLE Advice'
ORDER BYf.task_name;
删除segmentadvisor
execdbms_advisor.delete_task('BIG_TABLE Advice');
释放未使用的表的空间
alter tabletablename enable rowmovement;
alter tabletablename shrink spcace;
alter table tablename shrink spcace cascade;(级联收缩索引空间)
alter table tablename shrink spcace compact;(只整理碎片，不调整高水位线)--如果整理碎片和降低高水位话费时间会很长，用于分开操作