深入讲讲oracle数据库容量满了的解决方案

前言

在之前的工作过程中，有时会遇到oracle数据库容量满了的情况，以前我会选择扩容来解决这个问题，对于其他方式并没有太过于考虑，最近测试环境又出现了这个问题，而且测试环境服务器本身的存储空间也不多了，所以自己结合网上的文章以及oracle的官网文档，采用了手动降容的方式来解决这个问题。本篇文章我会把可供参考的解决思路分享给各位读者，希望对各位有所帮助。

数据库版本：Oracle19c
数据库连接工具：Sqlplus / DBeaver

一、如何判断自己数据库的表空间使用量

使用下面的语句，可以帮助我们快速了解当前数据库中各个表空间的容量。下面的语句，其实核心在于使用dba_segments视图来进行统计。

select upper(f.tablespace_name) "表空间名",
       d.tot_grootte_mb "表空间大小(m)", 
     d.tot_grootte_mb - f.total_bytes "已使用空间(m)",
      to_char(round((d.tot_grootte_mb - f.total_bytes) / d.tot_grootte_mb * 100,2),
     '990.99') || '%' "使用比", 
       f.total_bytes "空闲空间(m)",
       f.max_bytes "最大块(m)"
    from (select tablespace_name,
     round(sum(bytes) / (1024 * 1024), 2) total_bytes,
     round(max(bytes) / (1024 * 1024), 2) max_bytes
              from sys.dba_free_space
           group by tablespace_name) f,
         (select dd.tablespace_name,
               round(sum(dd.bytes) / (1024 * 1024), 2) tot_grootte_mb
          from sys.dba_data_files dd
         group by dd.tablespace_name) d
 where d.tablespace_name = f.tablespace_name
 order by 1

当某个表的表空间使用量已经达到了95%及以上的时候（如果是生产环境的话，个人觉得容量用了85%就要关注这个问题了），我们就要考虑怎么解决数据库空间即将不足的问题了。需要注意的是，因为数据库分成了不同的表空间，对于SYSTEM和SYSAUX空间我们一般是不需要去额外做管理的，我们把关注点放在USERS等其他自定义的表空间上面就行。

解决表空间的思路无外乎两个：开源或者节流

开源就是增加表空间容量，容量不够的话直接加上去就行
节流是指删除表空间中无效的数据块
（更加具体来说就是删除表中无效的测试数据以及压缩数据块）
两种解决思路的详情可以从下一章中去了解

二、解决表空间容量不足的问题

（一）解决思路一：表空间扩容

我们知道，表空间只是oracle的逻辑存储空间，实际上我们的数据是存储在.df文件中的。所以扩容的方式无非就是两种：① 给原来的df文件扩容、②追加新的数据文件

一般来说，我们可以先查看一下当前数据库原有的数据库文件来辅助我们决策，使用下面的sql我们可以对指定的表空间数据文件进行查询。这里核心是用到了dba_data_file和dba_free_space这两个视图。

 select a.tablespace_name,total,free,total-free used,a.file_name from
  ( select tablespace_name,sum(bytes)/1024/1024 total,file_name from dba_data_files group by tablespace_name,file_name) a,
  ( select tablespace_name,sum(bytes)/1024/1024 free from dba_free_space group by tablespace_name) b
where a.tablespace_name=b.tablespace_name
and a.tablespace_name='USERS';

一般来说，如果是非生产环境且原数据文件大小并没有很大的情况下，个人更推荐扩容的方法来解决；如果是生产环境，追加新的数据文件则更加稳妥。具体原因下文会进行介绍。

方式一：给原来的数据文件扩容

alter database datafile 'xxx/xx.dbf' resize totalSize;

举个例子：alter database datafile '/home/oracle/oradata/ORCL/pdb1/users.dbf' resize 2G;
这里的核心在于使用resize命令来进行空间扩容，而dbf文件的具体路径我们可以从上文中的sql结果集进行获取。

方式二：给表空间追加新的数据文件

alter tablespace tablespace_name add datafile 'xxx/xxx.dbf' size totalSize;

举个例子：alter tablespace USERS add datafile '/home/oracle/oradata/ORCL/pdb1/users3.dbf' size 2 G;

两种方式的优劣势比较

--	优点	缺点
resize扩容	只需要维护少量的df文件就行，便于管理	执行速度慢，在生产环境执行resize会有影响正常业务运行的风险
追加数据文件	不会影响应用运行，命令执行速度快	每次都追加文件的话不容易后期维护

（二）解决思路2：手动降容

方式一：删除无用的测试数据（针对非生产环境）

空间满了就把不需要的数据删掉腾出空间，这是我们很容易可以想到的解决方案。但实际上一删了之可能并不能实现我们预期的目标。我们删除数据的方式无外乎三种：drop、truncate和delete。回顾一下这三个命令的用法，drop会把包括表结构在内的所有数据删除，truncate保留表结构，删除表内所有数据，delete可以根据指定条件删除指定数据。

我们可以根据自己的实际需要，删除掉不需要的表结构等对象，但删除完成后就大功告成了吗？
NoNoNo，如果你重新执行一下第一章的语句你会发现表空间的占用率没有丝毫的降低！这是因为即使你删除了表数据，但实际上oracle并没有直接将这部分数据的空间释放出来。我们删除数据的目的并没有达成。

使用delete删除的数据
使用delete删除数据后，oracle会将被删除的数据对应的数据块标记为“未使用”，但并不会释放掉这一块的表空间。而是后续会优先让新的数据保存到这部分的空间上面，从而达到重复利用这部分空间的效果。但站在希望空间马上被回收的角度，这个机制是和我们的初衷相违背的，所以如果是通过delete删除的数据，我们还需要配合其他命令来实现空间回收的效果。
使用truncate删除数据
truncate在删除数据的同时还会自动释放表空间，可以说是理想的降容命令了。（但实际上除非确认整张表的数据都可以删除外，我们一般还是比较少有机会可以使用这个命令来释放空间的）。
ps：使用truncate清空表数据后，表的索引数据也会被同步删除和进行空间释放，表和索引默认会还原至min_events大小。
使用drop删除数据
drop在删除数据的同时也会自动释放表空间，但是相比对truncate命令来说，我们还需要重新进行建表的创建索引等相关操作。相对会麻烦一些。需要注意的是，如果数据库的回收站开关是开启的，那被drop掉的表数据会进入到回收站里面，我们需要额外在清空一下回收站的数据。（在下文的扩展章节中，会对这种场景的处理展开讲一下）
ps：使用drop删除表的同时也会同步删除掉所有的索引数据，对应的空间也会被释放掉。

如何即时刷新视图数据？

注意事项：我们删除完数据后，oracle并不会马上就重新刷新相关的对象的静态数据，我们可以通过下面的命令手动刷新一下表的静态数据：

对于oracle10g之后的版本
如果不加cascade的话，则默认只会更新表的数据信息，不会级联更新索引信息

BEGIN
  dbms_stats.gather_table_stats (  
    ownname=>'user_name',tabname=> 'table_name',cascade=>true
);
END;

对于oracle10g之前的版本，使用下面的语句来立即更新视图数据

ANALYZE  TABLE  TABLE_NAME COMPUTE  STATISTICS ;

方式二：整理磁盘空间

我们系统运行旧了，数据库的表肯定少不了各种删除数据、更新数据的操作，数据库表中难免会有着不同程度的碎片，对碎片进行整理并降低高水位线（注意，这里的降低高水位线很重要）也是降容的有效方式。

步骤一：查看水位线虚高较大的表

我们可以通过下面的语句来统计当前数据库中水位线虚高的表

SELECT
    table_name,
    ROUND ( (blocks * 8),2) "高水位空间 k",
    ROUND ( (num_rows * avg_row_len / 1024),2) "真实使用空间 k",
    ROUND ( (blocks * 10 / 100) * 8,2) "预留空间(pctfree) k",
    ROUND ( ( blocks * 8 - (num_rows * avg_row_len / 1024) - blocks * 8 * 10 / 100), 2) "浪费空间 k"
FROM
    dba_tables
WHERE
    TEMPORARY = 'N'
    AND OWNER = 'SKYPEARL_VERIFY'
    AND TABLESPACE_NAME = 'USERS'
    AND TABLE_NAME = 'XXX'

步骤二：降低高水位线

降低高水位线其实也算是一个经常被讨论的话题，可以实现的方式有很多，这里的话我主要列举三个可行性比较大，易操作的方案。

（1）使用`alter table table_name move`的方式重建表

这个命令完整的命令是ALTER TABLE TABLE_NAME MOVE TABLESPACE TABLESPACE_NAME，用于将表迁移到其他表空间中。如果不加后面的tablespace参数的话，则是移动在当前用户默认的表空间中。执行这个命令后，oracle会把包括表结构、索引、表数据等重新迁移到对应的表空间中。在这个过程中，表中对应的未使用数据块自然就得到了释放。
但是需要注意的是，如果是oracle11g及之前版本的数据库执行完这条命令后，表中的索引会失效，我们需要对表中存在的索引数据进行重建才行（具体的原因后面会讲到）。如果是oracle19c的话，可以不需要重建表索引

1. 重建表
ALTER TABLE TABLE_NAME MOVE TABLESPACE TABLESPACE_NAME;
2. 重建表索引
ALTER INDEX TABLESPACE_NAME.TABLE_NAME REBUILD;

（2）使用`shrink`命令压缩表空间（oracle10g之后的版本可用，推荐！）

在oracle10g之后，oracle提供了shrink命令来让我们手动降低高水位线，具体步骤和命令如下：

a.开启行记录的可移动配置

alter table table_name enable row movement;

b.执行表缩容

alter table table_name shrink space

如果希望级联压缩的话，用：alter table table_name shrink space CASCADE。但据我观察，级联压缩执行起来特别慢，不介意耗时太长的话，一直把命令挂在上面就行。

c.关闭可移动设置

ALTER TABLE TABLESPACE_NAME.TABLE_NAME DISABLE ROW MOVEMENT;

shrink提供了两个可选参数给我们去执行shrink命令

compact
表空间压缩的命令可以大致分成两步：磁盘数据整理+高水位线调整。默认情况下shrink命令会对这两个步骤一起进行处理，由于高水位线的调整涉及到短暂的锁表操作，如果希望对业务功能影响最小的话，我们可以先使用compact选项执行磁盘数据整理的操作，此时不会直接操作到高水位线，同时会将碎片整理和压缩结果保存到磁盘上，然后等到业务低峰期的时候再完整执行shrink命令就可以直接省去压缩磁盘的时间，直接调整高水位线了。

alter table table_name shrink space compact;
alter table table_name shrink space;

cascade
级联压缩，可以同时将表上面的索引数据也一并进行压缩

alter table table_name shrink space cascade;

相比于move命令来说，shrink命令不需要再重新建索引，也可以把shrink命令理解为是oracle帮你做了move表和rebuild索引的综合操作，但这个操作的持续时间会很长。

根据笔者亲测，shrink命令执行时间往往很长，一张1g多的表大概需要60s的时间才能整理完，当配合cascade命令一起执行的时候执行时间会相当长。但shrink的好处也十分突出，执行命令时不会影响数据库的正常运作，我们的数据表还是可以正常对外提供服务。所以如果时间不紧张的话，shrink会是一个相当不错的选择。

（3）手动重建表

这种方案是网上其他人提出来的，个人觉得应该也是可行的，但是没有实际操作过。具体方式如下：

手动复制需要整理空间的表并重新命名表名

CREATE TABLE New_Table_Name AS SELECT * FROM Old_Table_Name;

drop掉原来的表

DROP TABLE OLD_TABLE_NAME;

把新表改回旧表的名字

ALTER TABLE NEW_TABLE_NAME RENAME TO OLD_TABLE_NAME;

手动重新建索引和约束等数据

...

相信大家也发现了，这种方式本质上和move并没有区别，都是通过重建表的时候来释放占用的表空间。但是这种方式会更加繁琐，个人觉得可以的话，还是选择上文提到的另外两种方式来处理会更好。

虽然手动降容可以实现数据库容量瘦身的效果，但我们要意识到被我们整理出来的磁盘碎片本身也是会被表自身所使用的，只是相对来说过程会比较慢，我们只是人为干预了这个过程，先腾出这部分空间给其他表去使用来提高表空间的利用率。这个过程中本质上是提升表空间的利用率而不是表空间的容量！

扩展知识

（一）为什么使用`alter table move`方式后需要重建索引

我们可以先看看oracle11g的官方文档是怎么解释的：

Moving a table changes the rowids of the rows in the table. This causes indexes on the table to be marked UNUSABLE, and DML accessing the table using these indexes will receive an ORA-01502 error. The indexes on the table must be dropped or rebuilt. Likewise, any statistics for the table become invalid and new statistics should be collected after moving the table.

简单来说，当我们重建表后，由于数据实际上存储的物理空间发生了改变，所以rowid的值自然也就发生了变更。而索引本身就依赖了rowid来进行关联，所以一旦rowid发生变更而索引没有同步更新值的话，就会出现索引失效的问题。

但是在oracle12.2c之后，其实oracle官方就提供了UPDATE INDEXES参数来让我们可以同步更新索引，不再需要手动重建索引了（此处需要注意oracle12.1c还是需要自己rebuild索引的），甚至oracle还提供了online选项给我们来在线重建表，可以说是比较贴心了。

（二）数据库的回收站

处于对drop命令误操作的防范，oracle提供了回收站让我们来挽回被我们误删的数据，当然了，这个特性在我们想要立即删除某些表数据的时候会给我们造成一定的困扰。我们可以通过下面的命令来看当前的数据库是否有开启回收站

SELECT * FROM V$PARAMETER WHERE NAME = 'recyclebin'

若value为on，则说明回收站处于开启状态；若value为off，则说明回收站处理关闭状态

如何控制数据库回收站开关

ALTER SESSION SET recyclebin = ON; -- 启动回收站里的会话    
ALTER SYSTEM SET recyclebin = ON;  
ALTER SESSION SET recyclebin = OFF; -- 关闭回收站里的会话 
ALTER SYSTEM SET recyclebin = OFF;

怎么清空回收站数据？（生产环境慎用）

PURGE RECYCLEBIN; --清空当前用户回收站的所有数据
PURGE DBA_RECYCLEBIN; --清空DBA回收站的所有数据

-- 如果只希望删除特定的数据，可以参考下面的格式进行数据回收
PURGE TABLESPACE<<Table_NAME>> USER <<User_Name>>;  -- 清空特定用户的表空间对象
PURGE TABLESPACE<<Table_NAME>>;  -- 清空与该表空间有关联的对象
PURGE TABLE <<Table_NAME>>;  -- 清空特定表

更多和回收站有关的资料，可以看一下这篇文章：Oracle回收站介绍

小结

个人感觉两种解决oracle容量问题的思路中，扩容是王道，可以解决100%的问题，但扩容需需要我们提供更多的磁盘空间，提升我们的硬件开支；“降容”则是有效的辅助方式，在没有更多资源的前提下，尽可能的让原本的数据库容量得到更充分的利用，也是一种值得考虑的解决方案。
同时，如果各位读者有更加推荐的解决思路，也不妨一起在评论区进行探讨。
另外，本文比较多用到了DBA相关的视图，如果说实际上你拿不到这么高权限的用户的话，那么从USER下面的视图去进行分析，也是一种可行的分析思路。

posted @ 2023-10-07 17:15 moutory 阅读(2159) 评论(0) 编辑收藏举报来源

刷新页面返回顶部