Oracle空间回收

1、总述

在Oracle数据库中，在对表数据进行大量delete操作后，高水位不会自动回收，需要对表进行shrink，以提高查询效率，同时可以考虑对数据文件进行resize，以减少数据库总大小；

2、对表进行shrink

2.1、原理

segment shrink分为两个阶段：

1）、数据重组(compact)：通过一系列insert、delete操作，将数据尽量排列在段的前面。在这个过程中需要在表上加RX锁，即只在需要移动的行上加锁。由于涉及到rowid的改变，需要enable row movement.同时要disable基于rowid的trigger,这一过程对业务影响比较小。

2）、HWM调整：第二阶段是调整HWM位置，释放空闲数据块。此过程需要在表上加X锁，会造成表上的所有DML语句阻塞。在业务特别繁忙的系统上可能造成比较大的影响。

shrink space 语句两个阶段都执行。

shrink space compact 只执行第一个阶段。

如果系统业务比较繁忙，可以先执行shrink space compact重组数据,然后在业务不忙的时候再执行shrink space降低HWM释放空闲数据块。

shrink必须开启行迁移功能。

alter table table_name enable row movement ;

对于包含大字段的表，需要使用如下命令进行收缩；

alter table table_name modify lob(lob_column) (shrink space cascade) ;
复制

**或者使用在线重定义的方式进行空间收缩（可参考文档 **Doc ID 1394613.1）；

2.1.1、命令汇总

alter table table_name enable row movement ;
alter table table_name shrink space compact ;
alter table table_name shrink space cascade ; --加cascade，表上相应的索引也会同时进行收缩。
复制

2.2、自动段统计信息收集

在开启了自动统计信息收集后，oracle会自动给出段建议,同时给出相应的收缩命令，可以参考如下脚本输出：

select tablespace_name,
       segment_name,
       segment_type,
       partition_name,
       recommendations,
       regexp_substr(recommendations, '\d{1,}', 1) / 1024 / 1024 / 1024 sizef,
       c1
  from table(dbms_space.asa_recommendations('FALSE', 'FALSE', 'FALSE'))
 order by sizef desc;
复制

2.3、注意

收缩时会有大量的归档的日志产生，要保证有足够的归档空间；

2.4、批量收缩脚本样例

对表进行收缩，如果表中含有lob字段，同时收缩lob；

create or replace procedure shrink authid current_user as
  v_shrink_sql     varchar2(32767);
  v_shrink_lob_sql varchar2(32767);
  v_error_code     VARCHAR2(10); --错误码
  v_description    VARCHAR2(160); --错误信息
  cursor cur_shrinks is
    select t.owner, t.table_name
      from dba_tables t;
  cursor cur_lobs(v_owner varchar2, v_table_name varchar2) is
    select t.OWNER, t.TABLE_NAME, t.COLUMN_NAME, t.DATA_TYPE
      from dba_tab_columns t
     where t.DATA_TYPE = 'CLOB'
       and t.OWNER = v_owner
       and t.TABLE_NAME = v_table_name;
begin
  dbms_output.enable(buffer_size => null);
  for cur_shrink in cur_shrinks loop
    v_shrink_sql := 'alter table ' || cur_shrink.owner || '.' ||
                    cur_shrink.table_name || ' shrink space cascade ';
    dbms_output.put_line(v_shrink_sql || ';');
    begin
      execute immediate v_shrink_sql;
    exception
      when others then
        v_error_code  := SQLCODE;
        v_description := substr(sqlerrm, 1, 560);
        dbms_output.put_line(v_error_code || ';' || v_description);
        dbms_output.put_line('>>> error ' || v_shrink_sql);
    end;
    for cur_lob in cur_lobs(cur_shrink.owner, cur_shrink.table_name) loop
      v_shrink_lob_sql := 'alter table ' || cur_lob.owner || '.' ||
                          cur_lob.table_name || ' modify lob(' ||
                          cur_lob.column_name ||
                          ') (shrink space cascade) ';
      dbms_output.put_line(v_shrink_lob_sql || ';');
      begin
        execute immediate v_shrink_lob_sql;
      exception
        when others then
          v_error_code  := SQLCODE;
          v_description := substr(sqlerrm, 1, 560);
          dbms_output.put_line(v_error_code || ';' || v_description);
          dbms_output.put_line('>>> error ' || v_shrink_lob_sql);
      end;
    end loop;
  end loop;
end;

复制

3、对数据文件进行resize

在对表进行shrink后，可以考虑对数据文件进行resize；可使用如下脚本列出resize语句；

with
 hwm as (
  -- get highest block id from each datafiles ( from x$ktfbue as we don't need all joins from dba_extents )
  select /*+ materialize */ ktfbuesegtsn ts#,ktfbuefno relative_fno,max(ktfbuebno+ktfbueblks-1) hwm_blocks
  from sys.x$ktfbue group by ktfbuefno,ktfbuesegtsn
 ),
 hwmts as (
  -- join ts# with tablespace_name
  select name tablespace_name,relative_fno,hwm_blocks
  from hwm join v$tablespace using(ts#)
 ),
 hwmdf as (
  -- join with datafiles, put 5M minimum for datafiles with no extents
  select file_name,nvl(hwm_blocks*(bytes/blocks),5*1024*1024) hwm_bytes,bytes,autoextensible,maxbytes
  from hwmts right join dba_data_files using(tablespace_name,relative_fno)
 )
select
 case when autoextensible='YES' and maxbytes>=bytes
 then -- we generate resize statements only if autoextensible can grow back to current size
  '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
   ||'M from '||to_char(ceil(bytes/1024/1024),999999)||'M */ '
   ||'alter database datafile '''||file_name||''' resize '||ceil(hwm_bytes/1024/1024)||'M;'
 else -- generate only a comment when autoextensible is off
  '/* reclaim '||to_char(ceil((bytes-hwm_bytes)/1024/1024),999999)
   ||'M from '||to_char(ceil(bytes/1024/1024),999999)
   ||'M after setting autoextensible maxsize higher than current size for file '
   || file_name||' */'
 end SQL
from hwmdf
where
 bytes-hwm_bytes>1024*1024 -- resize only if at least 1MB can be reclaimed
order by bytes-hwm_bytes desc
复制