oracle数据库碎片化管理(四)
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 => 'SCOTT',
attr2 => 'TBLORDERS',
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;
/
---删除执行计划
declare name varchar2(100);
begin
name:='Manual_tblorders';
DBMS_ADVISOR.DELETE_TASK (name);
end;
/
---手动执行计划
declare name varchar2(100);
begin
name:='Manual_tblorders';
dbms_advisor.execute_task(name);
end;
/
NOTE:如果执行计划结果中已经有数据则不能直接手动执行需要删除再执行
---查看手动新建的计划是否已经执行完成
select task_id, task_name, status,advisor_name,created from dba_advisor_tasks
where owner = 'SYS' and task_name='Manual_tblorders' and advisor_name = 'Segment Advisor' ;
select af.task_name, ao.attr2 segname, ao.attr3 partition, ao.type, af.message
from dba_advisor_findings af, dba_advisor_objects ao
where ao.task_id = af.task_id
and ao.object_id = af.object_id
and af.task_id=&task_id;
----只查询可以进行shrink操作的对象
select f.task_name, o.attr2 segname, o.attr3 partition, o.type, f.message
from dba_advisor_findings f, dba_advisor_objects o
where o.object_id = f.object_id
and o.task_name=f.task_name
--and f.message like '%shrink%'
and f.message like '%收缩%'
and f.task_id=&task_id
order by f.impact desc;
---查看automatic segment advisor的recommendations结果
select tablespace_name, segment_name, segment_type, partition_name,
recommendations, c1 from
table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'));
********************************************************************************
5. 碎片整理方法
********************************************************************************
------------------------------------------------*
5.1表空间碎片整理
------------------------------------------------*
alter tablespace users coalesce;
------------------------------------------------*
5.2表碎片整理
------------------------------------------------*
---方法1:exo/imp或data pump数据泵技术
---方法2:CTAS
create table newtable as select * from oldtable;
drop table oldtable;
rename table newtable to oldtable;
----方法3:move tablespace技术
alter table
move tablespace ;
----方法4:shrink
alter table enable row movement;
alter table shrink space cascade; --压缩表以及相关数据段并下调HWM
alter table shrink space compact; --只压缩数据不下调HWM,不影响DML操作
alter table shrink space; --下调HWM,影响DML操作
----方法5:online redefinition
--online redefinition具有的应用场景:
1).Online table redefinition enables you to:
2).Modify the storage parameters of a table or cluster
3).Move a table or cluster to a different tablespace
4).Add or drop partitioning support (non-clustered tables only)
5).Change partition structure
6).Change physical properties of a single table partition, including moving it to a different tablespace in the same schema
7).Change physical properties of a materialized view log or an Oracle Streams Advanced Queueing queue table
8).Add support for parallel