Oracle段收缩功能

服务器 -> Oracle Scheduler -> 自动维护任务 -> 配置，来启用或禁用“段指导”任务，同时这里还可以设置“优化程序统计信息搜集”，“自动 SQL 优化” ## 2.2 使用SQL命令设置 ## ``` --禁用自动执行Segment Advisor BEGIN dbms_auto_task_admin.disable(client_name => 'auto space advisor', operation => NULL, window_name => NULL); END; / 
--启用自动执行Segment Advisor
 BEGIN
 dbms_auto_task_admin.enable(client_name => 'auto space advisor', operation => NULL, window_name => NULL);
 END;
 /

--启用行移动
 alter table T3 enable row movement;
 --与大多数Oracle SQL命令不同，添加COMPACT关键字后，实际执行的操作不多反少，即HWM不移动
 alter table T3 shrink space compact;
 --去掉COMPACT关键字后，执行HWM移动
 alter table T3 shrink space;
 --禁用行移动
 alter table T3 disable row movement;

--创建表T3
 create table T3 (c1 varchar2(4000));

--往T3插入1000行（使用大值）
 begin
 for i in 1..1000 loop
 insert into T3 select rpad(i,3900,'*') from dual;
 end loop;
 commit;
 end;
 /
 --T3大小
 select segment_name, BYTES/1024/1024 "MB" from user_segments where segment_name = 'T3';
 SEGMENT_NAME                           MB

T3                                      8
 --使用小值更新这些行
 update t3 set c1='1';
 commit;
 --此时再次查询表T3大小是不变的

variable task_id number
 declare
 name varchar2(100);
 descr varchar2(500);
 obj_id number;
 begin
 name := ''; --unique name
 descr := 'Check T3 table';
 dbms_advisor.create_task('Segment Advisor', :task_id, name, descr, null);
 dbms_advisor.create_object(name, 'TABLE', 'JINGYU', 'T3', null, null, obj_id);
 dbms_advisor.set_task_parameter(name, 'RECOMMEND_ALL', 'TRUE');
 dbms_advisor.execute_task(name);
 end;
 /

SQL> conn jingyu/jingyu
 Connected.
 SQL> variable task_id number
 SQL> declare
 2    name varchar2(100);
 3    descr varchar2(500);
 4    obj_id number;
 5  begin
 6    name := ''; --unique name
 7    descr := 'Check T3 table';
 8    dbms_advisor.create_task('Segment Advisor', :task_id, name, descr, null);
 9    dbms_advisor.create_object(name, 'TABLE', 'JINGYU', 'T3', null, null, obj_id);
 10    dbms_advisor.set_task_parameter(name, 'RECOMMEND_ALL', 'TRUE');
 11    dbms_advisor.execute_task(name);
 12  end;
 13  /
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.01
 SQL>
 SQL>
 SQL> print task_id

col owner for a20
 col task_name for a20
 col type for a20
 select owner, task_id, task_name, type, message, more_info from dba_advisor_findings where task_id=1358;
 OWNER                   TASK_ID TASK_NAME            TYPE

MESSAGE
MORE_INFO
JINGYU                     1358 TASK_1358            PROBLEM
 The free space in the object is less than 10MB.
 Allocated Space:8388608: Used Space:954000: Reclaimable Space :7434608:

alter table T3 enable row movement;
 alter table T3 shrink space compact;
 alter table T3 shrink space;
 alter table T3 disable row movement;
 --T3大小
 select segment_name, BYTES/1024/1024 "MB" from user_segments where segment_name = 'T3';
 SEGMENT_NAME                           MB

T3                                  .0625