清理Oracle SYSTEM01.DBF文件

我不是来掘金的
943 阅读3分钟

一起养成写作习惯！这是我参与「掘金日新计划 · 4 月更文挑战」的第3天，点击查看活动详情。

最近遇到一个问题：Oracle的SYSTEM01.DBF文件23个G了，这肯定是不正常的，因为我自己真正用了些啥自己心里大概有点B-TREE！磁盘空间也所剩不多了，索性就把它清理一下，顺便对这个过程做做记录。

网上了解了一下，再思考了一哈哈，应该是由于Oracle数据库的审计功能未进行关闭（默认打开），在使用过程中，有一个使用了多线程技术的项目，未使用连接池，导致频繁连接数据库，监听日志文件以及SYSTEM01.DBF文件急剧增大。但是SYSTEM01.DBF文价不能直接删除，删掉就启动不起来了。所以对这个文件进行分析，将能删除的部分删除。

SYSTEM01.DBF文件属于Oracle数据库系统的系统表空间文件，里面存有很多与系统有关的表，所以不能错误并粗鲁的删除文件，应该分析具体的那个表的占用过高，然后尝试分析原因，最后找到解决方案。

以sysdba登录连接到数据库（这里选择可视化工具，查询的结果方便直观的查看，命令行可观性不好）

image.png

查看表空间使用率：

 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 "使用率" desc;

image.png 其中SYSTEM表空间使用了大约23.7G的空间了

继续查询查看SYSTEM表空间中最大的段

SELECT *
 FROM (SELECT BYTES, segment_name, segment_type, owner
 FROM dba_segments
 WHERE tablespace_name = 'SYSTEM'
 ORDER BY BYTES DESC)
 WHERE ROWNUM < 10;

image.png
其中AUD$占用最多，大约22.96G。

AUD$表是Oracle数据库的审计记录存储表。对我个人而言，拿这个记录没啥用，所以可以将里面的数据记录删除。
直接将AUD$表进行truncate操作，将里面的数据请掉。

image.png
再次查看表空间占用情况

image.png
发现只是占用率变少了，但是磁盘空间并没有释放，仍然被系统表空间文件占用着。

查看表空间的数据文件实际占用磁盘空间情况，发现只有744M了。

-- 查看数据库文件实际占用空间大小
SELECT Ddf.File_Name, De.File_Id, SUM(De.Bytes) / 1024 / 1024 AS Mb
  FROM Dba_Extents De
  JOIN Dba_Data_Files Ddf
    ON De.File_Id = Ddf.File_Id
 GROUP BY Ddf.File_Name, De.File_Id;

image.png 修改系统表空间的大小，根据已经占用的空间来选择修改合适的表空间大小。此处占用744M，给它一个G吧！慷慨点！修改时以上面查询中的FILE_ID来定位修改

-- 修改表空间数据文件大小
alter database datafile 1 resize 1024M;

image.png 看到报错信息，意思就是存储了1024M以外的地方的值，猜测这个resize的大小是连续的。网上的解决方案是考虑到系统表空间中表比较多，且关系比较复杂，操作有风险，保险起见可以直接找出最小可收缩大小进行收缩。

查看数据库文件的最大block_id，即有多少个块

-- 从上面获得要缩小的文件id是1，查看此文件有多少个block
select max(block_id) from dba_extents where file_id=1;

image.png 数据块大小的单位是byte，8192bit = 8k

-- 查询每个数据块的大小，单位是 byte
select value from v$parameter where name='db_block_size';

image.png 计算出最小占用空间

-- 计算该datafile占用的物理空间：最大块id * 每块大小8k / 1024
select 1925376 * 8 / 1024 from dual;

image.png 重新修改大小为15042M，失败，估计不能严丝合缝的按照这个大小来改

image.png

改为15043M，成功 image.png 整体过程脚本：

--  1、查询表空间使用率
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 "使用率" desc;

-- 查询系统表空间中占用空间前十的表
SELECT *
FROM (SELECT BYTES, segment_name, segment_type, owner
      FROM dba_segments
      WHERE tablespace_name = 'SYSTEM'
      ORDER BY BYTES DESC)
WHERE ROWNUM < 10;

-- 剪切系统审计表
TRUNCATE TABLE AUD$;

-- 查看数据库文件实际占用空间大小
SELECT Ddf.File_Name, De.File_Id, SUM(De.Bytes) / 1024 / 1024 AS Mb
FROM Dba_Extents De
         JOIN Dba_Data_Files Ddf
              ON De.File_Id = Ddf.File_Id
GROUP BY Ddf.File_Name, De.File_Id;

-- 修改表空间数据文件大小
alter database datafile 1 resize 1024 M; --失败： ORA-03297: 文件包含在请求的 RESIZE 值以外使用的数据

/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~开始~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
/*解决方法1：根据当前数据块所占用空间来缩小*/
-- 从上面获得要缩小的文件id是1，查看此文件有多少个block
select max(block_id)
from dba_extents
where file_id = 1;

-- 查询每个数据块的大小，单位是 byte
select value
from v$parameter
where name = 'db_block_size';

-- 计算该datafile占用的物理空间：最大块id * 每块大小8k / 1024
select 1925376 * 8 / 1024
from dual;

-- 修改表空间数据文件大小
alter database datafile 1 resize 15043 M;
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~结束~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/

-- 查看某一个表占用的磁盘空间
SELECT T.SEGMENT_NAME, T.SEGMENT_TYPE, SUM(T.BYTES / 1024 / 1024) "占用空间(M)"
FROM DBA_SEGMENTS T
WHERE T.SEGMENT_TYPE = 'TABLE'
  AND T.SEGMENT_NAME = 'AUD$'
GROUP BY OWNER, T.SEGMENT_NAME, T.SEGMENT_TYPE;

/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~开始~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
/*解决方法2：将数据文件中涉及到的表以及索引先移动开，重置数据文件大小后再移动回来。*/
-- 在dba_extents找到与ID=1的数据文件相关的表及索引
SELECT DISTINCT SEGMENT_NAME, PARTITION_NAME, SEGMENT_TYPE
FROM DBA_EXTENTS
WHERE FILE_ID = 1;

-- 需移动的表数据
select DISTINCT 'alter table ' || owner || '.' || segment_name || ' move tablespace user_test;'
from dba_extents
where segment_type = 'TABLE'
  and file_id = 1;

-- 需移动的索引数据
select DISTINCT 'alter index ' || owner || '.' || segment_name || ' rebuild tablespace user_test;'
from dba_extents
where segment_type = 'INDEX'
  and file_id = 1;

-- 需移动的分区表数据
select DISTINCT 'alter table ' || owner || '.' || segment_name || ' move partition ' || partition_name ||
                ' tablespace user_test;'
from dba_extents
where segment_type = 'TABLE PARTITION'
  and file_id = 1;

-- 需移动的分区表索引数据
select DISTINCT 'alter index ' || owner || '.' || segment_name || ' rebuild partition ' || partition_name ||
                ' tablespace user_test;'
from dba_extents
where segment_type = 'INDEX PARTITION'
  and file_id = 1;
/*表太多，怕移出问题，终止*/
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~结束~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/

/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~开始~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/
/*解决方法3：找到最大块的信息，将其所属表截掉*/
-- 查询最大块的信息
SELECT OWNER, SEGMENT_NAME, SEGMENT_TYPE, TABLESPACE_NAME, EXTENT_ID
FROM DBA_EXTENTS
WHERE BLOCK_ID = (SELECT MAX(BLOCK_ID) FROM DBA_EXTENTS);

-- 查询I_H_OBJ#_COL#索引属于哪张表
SELECT i.TABLE_NAME
FROM dba_indexes i
WHERE INDEX_NAME = 'I_H_OBJ#_COL#';

-- 截断这张表
TRUNCATE TABLE HISTGRM$; -- ORA-00701: 无法变更热启动数据库所需的对象
/**/
/*~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~结束~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~*/

参考：
# oracle 表空间清理，system01.dbf文件过大。
# oracle缩减表空间大小,Oracle表空间释放与增加表空间大小

avatar
文章
阅读
粉丝