Oracle表空间碎片维护

mahan

表空间碎片说明

在Oracle数据库中，表空间碎片指的是表空间中存在未被充分利用的空间片段（extent）。
这些空间片段可能是由于数据的增删改操作导致，造成表空间中存在一些零散的、不连续的数据块。表空间碎片可能会影响数据库性能和空间利用效率，也就是存在空闲空间，但是无法被使用，造成业务在对数据库表的增删改时提示空间不足的问题。

二

Oracle存储逻辑结构

Oracle存储的逻辑结构由表空间、段、区间、块组成，区间是数据库存储空间分配的逻辑单位，由一些连续数据块组成。一个或多个区间又构成了一个段。当现有分配的区间被完全使用的时候，Oracle就会为段分配一个新的区间。
对于AutoAllocate管理的区间而言，你可以指定初始段的大小，然后由Oracle决定新增区间的大小，最小区间大小为64k。自动扩展空间的选择有64K，1M，8M，64M。

三

表空间监控

为了防止表空间碎片过高导致表空间不可用问题，我们优化了表空间监控语句，增加了碎片空间监控，也就是不再只是仅仅监控剩余空间，而是将剩余空间减去碎片空间，得出的才是可用空间；
因为每个库大小不一，表的增删改每次获取的空间大小不一致，我们采用最大指标64M来进行监控，当获取不到64M的连续数据块时（extent），这些不连续的数据块被认定为碎片。
以下是最新表空间监控语句：
select  a.TABLESPACE_NAME "TBNAME",
    a.SPLINTER_SPACE "GetTableSplinterSize",
    a.SP_RATE "GetTableSPSizePused"
  from
    (SELECT D.TABLESPACE_NAME,
        TO_CHAR(SPACE,'fm999990.99') SPACE,
        BLOCKS SUM_BLOCKS,
        TO_CHAR(SPACE - NVL(FREE_SPACE, 0),'FM999990.00') "USED_SPACE",
        TO_CHAR(ROUND((1 - NVL(FREE_SIZE, 0) TOTAL_SIZE) * 100, 2),'FM999990.00') "USED_RATE",
        TO_CHAR(FREE_SPACE,'FM999990.00') "FREE_SPACE",
        TO_CHAR(S.SPLINTER_SPACE,'fm999990.00') "SPLINTER_SPACE",
        TO_CHAR(ROUND((1 - NVL(F.FREE_SIZE-S.SPLINTER, 0) / D.TOTAL_SIZE) * 100, 2),'fm999990.99') "SP_RATE"
    FROM
      (SELECT TABLESPACE_NAME, SUM(BYTES) TOTAL_SIZE,ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE, SUM(BLOCKS) BLOCKS FROM DBA_DATA_FILES GROUP BY TABLESPACE_NAME) D,
      (SELECT TABLESPACE_NAME, SUM(BYTES) FREE_SIZE,ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE FROM DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F,
      (SELECT TABLESPACE_NAME,COUNT(*) SP_COUNT,SUM(BYTES) SPLINTER,round(sum(bytes)/1024/1024,2) SPLINTER_SPACE FROM DBA_FREE_SPACE WHERE BYTES/1024/1024 <64 GROUP BY TABLESPACE_NAME) S
      WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)
      AND D.TABLESPACE_NAME = S.TABLESPACE_NAME
      ORDER BY  "USED_RATE" DESC) a,
    dba_tablespaces b
  where a.tablespace_name = b.tablespace_name
  and a.tablespace_name not like 'UNDO%';

四

表空间可用空间不足处理方法

4.1 清理数据
可以清理业务过期数据，释放空间，增加可用空间：
Drop table T1 purge;

4.2 重组表
可以将表重建，重新获取连续空间，降低不连续空间的概率：
Create table t2 as select * from t1;
Drop table t1 purge;
Alter table t2 rename to t1;

4.3 申请扩容表空间
可以提扩容工单，扩容足够表空间防止空间不可用情况：
Alter tablespace tbs1 add datafile ‘+DATA’ size 30G;

五

碎片解决办法

5.1 创建新表空间test_tbs
大小大于需要移动的原表空间：
create tablespace test_tbs datafile '+DATA' SIZE 30G;
Alter tablespace test_tbs add datafile '+DATA' SIZE 30G;
Alter tablespace test_tbs add datafile '+DATA' SIZE 30G;

5.2 移动原表空间内的表
--批量脚本：
SELECT  'alter table '||OWNER||'.'||TABLE_NAME||' move
tablespace NEW_TABLESPACE;' FROM DBA_TABLES WHERE TABLESPACE_NAME = 'OLD_TABLESPACE';

--示例：
alter table U1.T1 move tablespace NEW_TABLESPACE;

如果有分区表：
select 'alter table ' ||table_owner|| '.' || table_name || '
move partition ' || partition_name || ' tablespace
NEW_TABLESPACE;' as move_sql from dba_tab_partitions where tablespace_name='OLD_TABLESPACE';

如果有子分区表：
select 'alter table ' ||table_owner|| '.' || table_name || '
move subpartition ' || SUBPARTITION_NAME || ' tablespace
NEW_TABLESPACE;' as move_sql from DBA_TAB_SUBPARTITIONS
where tablespace_name='OLD_TABLESPACE';

5.3 查看索引情况
select index_name,index_type,table_name,table_owner,table_type,uniq
ueness,status from dba_indexes where tablespace_name = 'OLD_TABLESPACE';

5.4 重建索引
select 'alter index ' ||OWNER||'.'||index_name||' rebuild
online;' from dba_indexes where status <> 'VALID';

如果有分区索引：
select 'alter index '||a.table_owner||'.' ||b.index_name||'
rebuild partition '||a.partition_name||' tablespace
NEW_TABLESPACE parallel 8 online; ' from dba_tab_partitions
a,dba_ind_partitions b where
a.partition_name=b.partition_name and
a.tablespace_name='OLD_TABLESPACE';

如果有子分区索引：
select 'alter index '||a.table_owner||'.' ||b.index_name||'
rebuild subpartition '||a.partition_name||' tablespace NEW_TABLESPACE parallel 8 online; ' from
dba_tab_subpartitions a,dba_ind_subpartitions b where
a.partition_name=b.partition_name and
a.subpartition_name=b.subpartition_name and
a.tablespace_name='OLD_TABLESPACE';

如果有lob字段：
select 'alter table '||owner||'.'||table_name||
' move lob('||COLUMN_NAME||') store as (NEW_TABLESPACE);'from dba_lobs where TABLESPACE_NAME='OLD_TABLESPACE';

如果有需要，可以将这些表，索引等对象重新移动回原表空间内，然后删除中转的表空间；如果直接使用新表空间，可以将原表空间的大小resize回收到ASM磁盘组即可（建议DBA操作）。