云栈社区»论坛 › 站务中心「 Forum Service 」 › Oracle 11g/12c 系统表空间告急？审计表AUD$空间清理与迁移实战 ...

发回帖发新帖

1023 积分	0 好友	131 主题

发消息

Oracle 11g/12c 系统表空间告急？审计表AUD$空间清理与迁移实战

发表于 17 小时前 | 查看: 1| 回复: 0

你是否遇到过Oracle数据库的SYSTEM或SYSAUX表空间使用率莫名飙升，甚至触发告警？很多时候，这个“罪魁祸首”就是默默记录所有操作痕迹的系统审计表。本文将带你一步步诊断并解决因审计表（Oracle 11g的SYS.AUD$，12c及以后的AUDSYS.AUD$UNIFIED）过度膨胀导致的表空间占用过高问题。

一、系统表空间使用情况检查

首先，我们需要确认问题是否确实由系统表空间使用率过高引起，并定位主要的空间消耗者。

对于Oracle 11g版本，使用以下SQL检查系统表空间使用率：

break on con_id on db_name
set lines 500 pages 9999
col con_id head CON_ID for 999
col db_name for a15
col tablespace_name for a15
col avg_add_day for a11
col use_dayss for a9
with tbs_usage as
 (select 0 con_id,
         v.name db_name,
         b.tablespace_name,
         sum(case when b.maxbytes = 0 then b.bytes else b.maxbytes end) total,
         sum(b.bytes) - sum(a.bytes) used,
         sum(case when b.maxbytes = 0 then b.bytes else b.maxbytes end) - (sum(b.bytes) - sum(a.bytes)) free
  from (select tablespace_name, file_id, sum(bytes) bytes
          from dba_free_space
         group by tablespace_name, file_id) a,
         dba_data_files b,
         v$database v
  where a.file_id(+) = b.file_id
    and a.tablespace_name(+) = b.tablespace_name
  group by v.name, b.tablespace_name),
use_per_day as
 (select 0 con_id,
         name,
         (case when avg(use_per_day_mb) > 0 then avg(use_per_day_mb) else 0.001 end) avg_use_per_day_mb
  from (select x.name,
               x.rdate,
               nvl((x.used_blocks - lag(x.used_blocks) over(partition by x.name order by x.rdate)) * block_size / 1024 / 1024, 0) use_per_day_mb
          from (select b.name,
                       to_char(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'), 'yyyy-mm-dd') rdate,
                       max(tablespace_usedsize) used_blocks
                  from dba_hist_tbspc_space_usage a, v$tablespace b
                 where a.tablespace_id = b.ts#
                   and b.name in ('SYSAUX', 'SYSTEM', 'USERS')
                 group by b.name, to_char(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'), 'yyyy-mm-dd')) x,
               dba_tablespaces y
         where x.name = y.tablespace_name)
  group by name)
select u.con_id,
       u.db_name,
       u.tablespace_name,
       round(u.total / 1024 / 1024 / 1024) total_gb,
       round(u.used / 1024 / 1024 / 1024) used_gb,
       round(u.free / 1024 / 1024 / 1024) free_gb,
       round((u.used / u.total) * 100, 2) use_precent,
       case
         when p.avg_use_per_day_mb > 1024 then
          round(p.avg_use_per_day_mb / 1024, 1) || 'G'
         else
          round(p.avg_use_per_day_mb, 1) || 'M'
       end avg_add_day,
       case
         when u.free / p.avg_use_per_day_mb / 1024 / 1024 < 30 then
          round(u.free / p.avg_use_per_day_mb / 1024 / 1024) || 'd'
         else
          '>30d'
       end use_dayss
  from tbs_usage u, use_per_day p
 where u.tablespace_name = p.name
 order by con_id, tablespace_name;

如果想看更简洁的空间使用明细，可以运行：

break on con_id on db_name
set lines 500 pages 9999
col con_id head CON_ID for 999
col db_name for a15
col tablespace_name for a15
col sum_mb for 99999999
col used_mb for 99999999
col free_mb for 99999999
col extensible_mb for 9999999999999
col used_pct for a8
col free_pct for a8
col extensible_pct for a14
select 0 con_id,
       v.name db_name,
       d.tablespace_name,
       round(sum(case when d.maxbytes = 0 then d.bytes else d.maxbytes end) / 1024 / 1024, 2) sum_mb,
       round(sum(d.bytes / 1024 / 1024) - sum(f.bytes / 1024 / 1024), 2) used_mb,
       round((sum(d.bytes / 1024 / 1024) - sum(f.bytes / 1024 / 1024)) / (sum(case when d.maxbytes = 0 then d.bytes else d.maxbytes end) / 1024 / 1024), 4) * 100 || '%' used_pct,
       round(sum(case when d.maxbytes = 0 then d.bytes else d.maxbytes end) / 1024 / 1024 - (sum(d.bytes / 1024 / 1024) - sum(f.bytes / 1024 / 1024)), 2) free_mb,
       round((sum(case when d.maxbytes = 0 then d.bytes else d.maxbytes end) / 1024 / 1024 - (sum(d.bytes / 1024 / 1024) - sum(f.bytes / 1024 / 1024))) / (sum(case when d.maxbytes = 0 then d.bytes else d.maxbytes end) / 1024 / 1024), 4) * 100 || '%' free_pct,
       round(sum(case when d.maxbytes = 0 then d.bytes else d.maxbytes end) / 1024 / 1024 - sum(d.bytes / 1024 / 1024), 2) extensible_mb,
       round((sum(case when d.maxbytes = 0 then d.bytes else d.maxbytes end) / 1024 / 1024 - sum(d.bytes / 1024 / 1024)) / (sum(case when d.maxbytes = 0 then d.bytes else d.maxbytes end) / 1024 / 1024), 4) * 100 || '%' extensible_pct
  from (select tablespace_name, file_id, sum(bytes) bytes
          from dba_free_space
         group by tablespace_name, file_id) f,
       dba_data_files d,
       v$database v
 where f.file_id(+) = d.file_id
   and f.tablespace_name(+) = d.tablespace_name
   and f.tablespace_name in ('SYSAUX', 'SYSTEM', 'USERS')
 group by v.name, d.tablespace_name
 order by con_id, tablespace_name;

对于Oracle 12c及之后版本（支持多租户架构），需要使用CDB视图进行检查：

break on con_id on db_name
set lines 500 pages 9999
col con_id head CON_ID for 999
col db_name for a15
col tablespace_name for a15
col avg_add_day for a11
col use_dayss for a9
with tbs_usage as
 (select v.con_id,
         v.name db_name,
         b.tablespace_name,
         sum(case when b.maxbytes = 0 then b.bytes else b.maxbytes end) total,
         sum(b.bytes) - sum(a.bytes) used,
         sum(case when b.maxbytes = 0 then b.bytes else b.maxbytes end) - (sum(b.bytes) - sum(a.bytes)) free
  from (select con_id, tablespace_name, file_id, sum(bytes) bytes
          from cdb_free_space
         group by con_id, tablespace_name, file_id) a,
         cdb_data_files b,
         v$containers v
  where a.file_id(+) = b.file_id
    and a.tablespace_name(+) = b.tablespace_name
    and b.con_id = v.con_id
  group by v.con_id, v.name, b.tablespace_name),
use_per_day as
 (select con_id,
         name,
         (case when avg(use_per_day_mb) > 0 then avg(use_per_day_mb) else 0.001 end) avg_use_per_day_mb
  from (select x.con_id,
               x.name,
               x.rdate,
               nvl((x.used_blocks - lag(x.used_blocks) over(partition by x.con_id, x.name order by x.rdate)) * block_size / 1024 / 1024, 0) use_per_day_mb
          from (select b.con_id,
                       b.name,
                       to_char(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'), 'yyyy-mm-dd') rdate,
                       max(tablespace_usedsize) used_blocks
                  from dba_hist_tbspc_space_usage a, v$tablespace b
                 where a.tablespace_id = b.ts#
                   and b.name in ('SYSAUX', 'SYSTEM', 'USERS')
                   and a.con_id = b.con_id
                 group by b.con_id, b.name, to_char(to_date(rtime, 'mm/dd/yyyy hh24:mi:ss'), 'yyyy-mm-dd')) x,
               cdb_tablespaces y
         where x.name = y.tablespace_name
           and x.con_id = y.con_id)
  group by con_id, name)
select u.con_id,
       u.db_name,
       u.tablespace_name,
       round(u.total / 1024 / 1024 / 1024) total_gb,
       round(u.used / 1024 / 1024 / 1024) used_gb,
       round(u.free / 1024 / 1024 / 1024) free_gb,
       round((u.used / u.total) * 100, 2) use_precent,
       case
         when p.avg_use_per_day_mb > 1024 then
          round(p.avg_use_per_day_mb / 1024, 1) || 'G'
         else
          round(p.avg_use_per_day_mb, 1) || 'M'
       end avg_add_day,
       case
         when u.free / p.avg_use_per_day_mb / 1024 / 1024 < 30 then
          round(u.free / p.avg_use_per_day_mb / 1024 / 1024) || 'd'
         else
          '>30d'
       end use_dayss
  from tbs_usage u, use_per_day p
 where u.tablespace_name = p.name
   and u.con_id = p.con_id
 order by con_id, tablespace_name;

其对应的简洁空间明细查询如下：

break on con_id on db_name
set lines 500 pages 9999
col con_id head CON_ID for 999
col db_name for a15
col tablespace_name for a15
col sum_mb for 99999999
col used_mb for 99999999
col free_mb for 99999999
col extensible_mb for 9999999999999
col used_pct for a8
col free_pct for a8
col extensible_pct for a14
select v.con_id,
       v.name db_name,
       d.tablespace_name,
       round(sum(case when d.maxbytes = 0 then d.bytes else d.maxbytes end) / 1024 / 1024, 2) sum_mb,
       round(sum(d.bytes / 1024 / 1024) - sum(f.bytes / 1024 / 1024), 2) used_mb,
       round((sum(d.bytes / 1024 / 1024) - sum(f.bytes / 1024 / 1024)) / (sum(case when d.maxbytes = 0 then d.bytes else d.maxbytes end) / 1024 / 1024), 4) * 100 || '%' used_pct,
       round(sum(case when d.maxbytes = 0 then d.bytes else d.maxbytes end) / 1024 / 1024 - (sum(d.bytes / 1024 / 1024) - sum(f.bytes / 1024 / 1024)), 2) free_mb,
       round((sum(case when d.maxbytes = 0 then d.bytes else d.maxbytes end) / 1024 / 1024 - (sum(d.bytes / 1024 / 1024) - sum(f.bytes / 1024 / 1024))) / (sum(case when d.maxbytes = 0 then d.bytes else d.maxbytes end) / 1024 / 1024), 4) * 100 || '%' free_pct,
       round(sum(case when d.maxbytes = 0 then d.bytes else d.maxbytes end) / 1024 / 1024 - sum(d.bytes / 1024 / 1024), 2) extensible_mb,
       round((sum(case when d.maxbytes = 0 then d.bytes else d.maxbytes end) / 1024 / 1024 - sum(d.bytes / 1024 / 1024)) / (sum(case when d.maxbytes = 0 then d.bytes else d.maxbytes end) / 1024 / 1024), 4) * 100 || '%' extensible_pct
  from (select con_id, tablespace_name, file_id, sum(bytes) bytes
          from cdb_free_space
         group by con_id, tablespace_name, file_id) f,
       cdb_data_files d,
       v$containers v
 where f.file_id(+) = d.file_id
   and f.tablespace_name(+) = d.tablespace_name
   and f.tablespace_name in ('SYSAUX', 'SYSTEM', 'USERS')
   and d.con_id = v.con_id
 group by v.con_id, v.name, d.tablespace_name
 order by con_id, tablespace_name;

在确认了是SYSTEM或SYSAUX表空间告急后，需要进一步查看是哪些对象占用了大量空间。以下是通用的检查脚本：

检查表空间内对象类型及大小分布：

set lines 300 pages 9999 feedback on
col owner for a20
col segment_type for a20
col tablespace_name for a15
select owner,
       segment_type,
       tablespace_name,
       count(*) cnt,
       round(sum(bytes) / 1024 / 1024, 2) sum_mb,
       round(sum(bytes) / 1024 / 1024 / 1024, 2) sum_gb
  from dba_segments
 where tablespace_name = upper('&tablespace_name')
 group by owner, segment_type, tablespace_name
 order by sum_mb desc, cnt desc, owner, segment_type;

检查表空间内占用空间最多的前100个对象：

set lines 300 pages 9999 feedback on
col owner for a20
col segment_name for a30
col segment_type for a20
col tablespace_name for a15
select * from (
select owner,
       segment_name,
       segment_type,
       tablespace_name,
       round(sum(bytes) / 1024 / 1024, 2) sum_mb,
       round(sum(bytes) / 1024 / 1024 / 1024, 2) sum_gb
  from dba_segments
 where tablespace_name = upper('&tablespace_name')
 group by owner, segment_name, segment_type, tablespace_name
 order by sum_mb desc, owner, segment_type, segment_name)
where rownum <= 100;

通常，运行到这里你会发现SYS.AUD$或AUDSYS.AUD$UNIFIED表及其索引、LOB段名列前茅，这就是问题的关键。

二、审计配置及审计表检查

为什么审计表会变得如此庞大？我们先检查一下审计功能的配置。

查看审计相关参数：

show parameter audit

从Oracle 11g开始，数据库默认开启了审计功能。参数audit_trail的默认值为DB，这意味着所有非SYS用户的审计记录都会被写入数据库的系统审计表中（11g是SYS.AUD$，12c后是AUDSYS.AUD$UNIFIED），而SYS用户的审计记录则写入由audit_file_dest参数指定的操作系统日志文件中。如果不加管理，审计表就会随着时间的推移无限增长。

接下来，查看审计表的具体信息：

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';
set lines 300 pages 999
col owner for a10
col table_name for a30
col tablespace_name for a15
col degree for 99
select owner,
       table_name,
       tablespace_name,
       ini_trans,
       pct_free,
       pct_used,
       num_rows,
       last_analyzed,
       partitioned,
       row_movement,
       degree
  from dba_tables
 where table_name like '%AUD%'
 order by owner, table_name;

审计表数据量检查：

对于Oracle 11g版本：

select count(*) from SYS.AUD$;
alter session set nls_date_format = 'yyyy-mm-dd hh24:mi:ss';
col min_time for a20
col max_time for a20
select min(TIMESTAMP#) min_time, max(TIMESTAMP#) max_time from SYS.AUD$;

对于Oracle 12c及之后版本：

select count(*) from AUDSYS.AUD$UNIFIED;
col min_time for a20
col max_time for a20
select min(EVENT_TIMESTAMP+0) min_time, max(EVENT_TIMESTAMP+0) max_time from AUDSYS.AUD$UNIFIED;

审计表及其相关段（表、索引、LOB）的大小检查：

这是一个更全面的查询，可以精确计算审计表占用的总空间。

对于Oracle 11g版本（SYS.AUD$）：

set lines 300 pages 9999
col owner for a6
col table_name for a15
col comments for a70
with temp1 as
 (select owner,
         segment_name,
         segment_type,
         round(sum(bytes) / 1024 / 1024 / 1024, 2) as segment_g
    from dba_segments
   where owner = 'SYS'
     and segment_name not like '%BIN$%'
   group by owner, segment_name, segment_type
   order by owner, segment_name, segment_type)
select a.owner, a.table_name, b.comments, c.partitioned, round(sum(total_g) * 1024, 2) as sum_mb, sum(total_g) as sum_gb
  from (select temp1.owner,
               segment_name as table_name,
               segment_g    as total_g
          from temp1
         where segment_type in ('TABLE PARTITION', 'TABLE')
           and segment_name = 'AUD$'
        union all
        select i.owner,
               i.table_name,
               sum(temp1.segment_g) total_g
          from dba_indexes i, temp1
         where i.owner = temp1.owner
           and i.owner = 'SYS'
           and i.table_name = 'AUD$'
          和 i.index_name = temp1.segment_name
         group by i.owner, i.table_name
        union all
        select temp1.owner,
               l.table_name,
               sum(temp1.segment_g) as total_g
          from dba_lobs l, temp1
         where l.owner = temp1.owner
           and l.owner = 'SYS'
           and l.table_name = 'AUD$'
           and temp1.segment_name = l.segment_name
         group by temp1.owner, l.table_name) a,
       dba_tab_comments b,
       dba_tables c
 where a.owner = b.owner
   and a.table_name = b.table_name
   and a.owner = c.owner
   and a.table_name = c.table_name
 group by a.owner, a.table_name, b.comments, c.partitioned
 order by sum_mb desc;

对于Oracle 12c及之后版本（AUDSYS.AUD$UNIFIED）：

set lines 300 pages 9999
col owner for a6
col table_name for a15
col comments for a70
with temp1 as
 (select owner,
         segment_name,
         segment_type,
         round(sum(bytes) / 1024 / 1024 / 1024, 2) as segment_g
    from dba_segments
   where owner in ('AUDSYS')
     and segment_name not like '%BIN$%'
   group by owner, segment_name, segment_type
   order by owner, segment_name, segment_type)
select a.owner, a.table_name, b.comments, c.partitioned, round(sum(total_g) * 1024, 2) as sum_mb, sum(total_g) as sum_gb
  from (select temp1.owner,
               segment_name as table_name,
               segment_g    as total_g
          from temp1
         where segment_type in ('TABLE PARTITION', 'TABLE')
        union all
        select i.owner,
               i.table_name,
               sum(temp1.segment_g) total_g
          from dba_indexes i, temp1
         where i.owner = temp1.owner
           and i.owner in ('AUDSYS')
           and i.index_name = temp1.segment_name
         group by i.owner, i.table_name
        union all
        select temp1.owner,
               l.table_name,
               sum(temp1.segment_g) as total_g
          from dba_lobs l, temp1
         where l.owner = temp1.owner
           and l.owner in ('AUDSYS')
           and temp1.segment_name = l.segment_name
         group by temp1.owner, l.table_name) a,
       dba_tab_comments b,
       dba_tables c
 where a.owner = b.owner
   and a.table_name = b.table_name
   and a.owner = c.owner
  和 a.table_name = c.table_name
 group by a.owner, a.table_name, b.comments, c.partitioned
 order by sum_mb desc;

三、审计表处理方案

确认审计表是“元凶”后，我们有几种处理方案。

方法1：直接清理审计表（释放空间）

这是最直接的方法，但会丢失所有历史审计记录。请确保业务或合规部门允许这样做。

对于Oracle 11g版本： 直接截断表。
```
truncate table sys.aud$;
```

对于Oracle 12c及之后版本： 必须使用Oracle提供的包DBMS_AUDIT_MGMT在根容器(CDB$ROOT)下进行清理。

begin
   dbms_audit_mgmt.clean_audit_trail(
      audit_trail_type => dbms_audit_mgmt.audit_trail_unified,
      use_last_arch_timestamp => False);
end;
/

方法2：迁移审计表到其他表空间（治本）

如果审计记录需要长期保留，清理不是办法。更好的策略是将审计表迁移到单独的表空间，避免影响系统表空间。这就像为MySQL或PostgreSQL的日志文件单独挂载一个磁盘一样，是运维中常见的隔离手段。

创建一个新的、足够大的表空间（例如：AUDIT_DATA）用于存放审计数据。
执行迁移（以下示例针对11g标准审计表，12c统一审计使用audit_trail_unified）：
```
begin
 dbms_audit_mgmt.set_audit_trail_location(
  audit_trail_type => dbms_audit_mgmt.audit_trail_aud_std,
  audit_trail_location_value => '&aud_tbs_name');
end;
/
```
执行此过程后，Oracle会自动将SYS.AUD$表及其相关索引、LOB段移动到指定的表空间。

处理后的思考

解决完紧急的空间问题后，更重要的是建立长期的预防机制。你应该评估当前的审计策略：是否记录了过多不必要的操作？是否需要设置审计记录的自动清理策略（如DBMS_AUDIT_MGMT包中的SET_LAST_ARCHIVE_TIMESTAMP和CLEAN_AUDIT_TRAIL过程结合使用）？

数据库的稳定运行离不开细致的管理和监控。希望这篇实战指南能帮助你顺利解决审计表空间占用问题。如果你在Oracle、MySQL或其他数据库的运维管理中遇到其他棘手问题，欢迎到云栈社区的技术论坛与更多同行交流探讨。

上一篇：如何基于RT-Thread与NXP MCXA156开发板实现USB HID游戏手柄
下一篇：OceanBase SQL执行超时：默认timeout时间是多少秒？

Oracle, 数据库审计, 表空间管理, 性能优化, 运维管理

Oracle 11g/12c 系统表空间告急？审计表AUD$空间清理与迁移实战

一、系统表空间使用情况检查

二、审计配置及审计表检查

三、审计表处理方案

相关帖子