1、数据库基础内容

表空间-数据文件-段-区-块

一个表空间由一个或者多个数据文件组成

高水位线和表碎片的示意图

其中被划掉的字代表delete删除，其中 耶 就是后续的insert，只会在末尾增加，而不是填充被删除的字段，这样就会导致数据库在搜寻数据时会浪费很多资源。

整理碎片后

大概是这个意思

2、正式操作

2.1问题描述

  服务器Centos7.6 + Oracle 11.2.0.4 + 23年10月最新补丁，数据库表空间使用率34%就报警空间不足，这个问题在以前的文章我写过，但是以前就写了如何解决，但是具体的原理却不太清楚，这次不仅要知其然，还要知其所以然。

2.2处理步骤

还是这套库，每次整理完碎片大概两个月就会报空间不足一次，这次刚过了一个月咱们就处理一下，虽然整理碎片的效果没有那么好，但是也足以说明一些问题

2.2.1首先是检查表空间大小

select a.tablespace_name,
       round(a.bytes / 1024 / 1024 /  1024, 0) "sum G",
       round((a.bytes - b.bytes) / 1024 /  1024 / 1024, 0) "used G",
       round(b.bytes / 1024 / 1024 /  1024, 0) "free G",
       round(((a.bytes - b.bytes) /  a.bytes) * 100, 2) "used%"
  from (select tablespace_name,  sum(bytes) bytes
          from dba_data_files
         group by tablespace_name) a,
       (select tablespace_name,  sum(bytes) bytes, max(bytes) largest
          from dba_free_space
         group by tablespace_name) b
where a.tablespace_name =  b.tablespace_name
order by ((a.bytes - b.bytes) / a.bytes)  desc;

可以看到，702G的表空间只使用了153G，还剩下549G，使用率21.73%

2.2.2检查表空间碎片查询

FSFI的值越小，表空间碎片越多，当小于30%说明碎片很可观了。

SELECT a.tablespace_name,
       round(sqrt(MAX(a.blocks) / SUM(a.blocks)) * (100 / sqrt(sqrt(COUNT(a.blocks)))),2) "FSFI(碎片率)"
  FROM dba_free_space  a,
       dba_tablespaces b
WHERE a.tablespace_name = b.tablespace_name
   AND b.contents NOT IN ('TEMPORARY',
                          'UNDO')
GROUP BY a.tablespace_name
ORDER BY 2;

第一行是咱们需要关注的表空间，碎片率高的吓人

2.2.3收集统计信息

exec dbms_stats.gather_schema_stats(ownname => '用户',estimate_percent => 80,method_opt => 'for all columns size repeat',no_invalidate => FALSE,degree => 16, granularity => 'ALL',cascade => TRUE);

收集统计信息有利于咱们进行前后比较好的对比

2.2.4整理碎片前的空间统计

select file_id,bytes/1024/1024,count(1) from dba_free_space where tablespace_name='CWDATA1' group by file_id,bytes/1024/1024 order by 3 asc;

这个是计算数据文件剩余空间大于1M的情况。这个是大概看一眼心里有谱就行。

这个是大于1M的条数和小于1M条数的统计

select sum(case when bytes/1024/1024 >=1 then 1 else 0 end) "greater then",sum(case when bytes/1024/1024 <1 then 1 else 0 end) "less then" from dba_free_space where tablespace_name='CWDATA1' ;

这个是计算大于1M的空间，代表着能使用的空间

select round(sum(bytes/1024/1024),2) from dba_free_space where tablespace_name='CWDATA1' and bytes/1024/1024 >=1;

这个是计算小于1M的空间，代表着不能使用的空间

select round(sum(bytes/1024/1024),2) from dba_free_space where tablespace_name='CWDATA1' and bytes/1024/1024 < 1;

2.2.5查询表的碎片率

round((BLOCKS * 8192 / 1024 / 1024),2) 这个判断条件要根据实际情况来，你说就分给他1M的空间，他再怎么浪费，你整理碎片也没意义。

SELECT TABLE_NAME,
       round((BLOCKS * 8192 / 1024 / 1024),2) "使用大小M",
       round((NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9),2) "实际大小M",
       round((NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9) / (BLOCKS * 8192 / 1024 / 1024),3) * 100 || '%' "实际使用率%"
  FROM DBA_TABLES
where blocks > 100
and tablespace_name='表空间'
   and (NUM_ROWS * AVG_ROW_LEN / 1024 / 1024 / 0.9) /
       (BLOCKS * 8192 / 1024 / 1024) < 0.3
   and round((BLOCKS * 8192 / 1024 / 1024),2) > 100
order by 2 desc;

2.2.6 move表

alter table AA move;

我这里没加具体的表空间，就代表这个是原表空间移动，不管你是用这个表的用户去操作，还是用系统用户去操作，都会在这个表的原表空间移动，所以不用担心用系统用户move表会移动到system表空间。

我这里之所以选择move因为原因如下

1、这几个表都我都选择了在业务低峰期操作，不会影响业务

2、而且本身表属于小表但是撑大了，不会因为move而花费太多时间

3、就算没有dba权限，也能在本用户下操作，不涉及服务器

2.2.7 重建失效的索引

select 'alter index ' || owner || '.' || INDEX_NAME || ' rebuild ONLINE PARALLEL 6;'
  from dba_indexes
where
--owner ='用户' and
status = 'UNUSABLE';

2.2.8 再次收集这些具体表的统计信息

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME =>  '用户',TABNAME => '表',ESTIMATE_PERCENT => 100,METHOD_OPT => 'for  all columns size repeat',no_invalidate => FALSE,DEGREE => 8,GRANULARITY => 'ALL',CASCADE => TRUE);

查询数据文件分区1M的区，这个是条数

select sum(case when bytes/1024/1024 >=1 then 1 else 0 end) "greater then",sum(case when bytes/1024/1024 <1 then 1 else 0 end) "less then" from dba_free_space where tablespace_name='表空间' ;

操作前

操作后

你可以发现大于1M的空间大大增多了，小于1M的空间大大减少了

这个是计算大于1M的空间

select round(sum(bytes/1024/1024),2) from dba_free_space where tablespace_name='表空间' and bytes/1024/1024 >=1;

操作前

操作后

适用空间也增大了

这个是计算小于1M的空间

select round(sum(bytes/1024/1024),2) from dba_free_space where tablespace_name='表空间' and bytes/1024/1024 < 1;

操作前

操作后

小于1M的空间也减少了