Oracle 报错 ORA-01653

这是我参与8月更文挑战的第13天，活动详情查看：8月更文挑战

生产环境 Oracle 部署完成后，若参数既没进行优化，又没专人对数据库进行日常运维，这种报错问题是很难避免的，无非是时间长短而已，业务越频繁，上线后触发的时间越快。
Oracle 建库后参数优化可参考：juejin.cn/post/699141…

1、问题：

数据库连接缓慢或连接报错，报错类似如下：

ORA-00604: error occurred at recursive SQL level 1 
ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM 
ORA-02002: error while writing to audit trail 
ORA-00604: error occurred at recursive SQL level 1 
ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM 
复制代码

2、原因：

Oracle system表空间耗尽。system表空间默认可自动扩展至32GB，空间耗尽原因如下：

1） 业务数据存放在system中。

2） Oracle数据库审计开启，审计日志撑爆system表空间。

3、解决方式：

Windows 和 Linux 环境均适用。

1） 将业务数据移至其他业务表空间。

2） 清理审计日志。

查询表空间使用情况（含可扩展）

set linesize 150
set pagesize 500
column file_name format a65
column tablespace_name format a25
column top_no_extend format a13
column top_extend format a10
column free_extend format a11
column used format a8
column used_extend format a11
select f.tablespace_name tablespace_name,
to_char(round((d.sumbytes/1024/1024/1024),2)) || 'GB' top_no_extend,
to_char(round(((d.sumbytes+d.extend_bytes)/1024/1024/1024),2)) || 'GB' top_extend,
to_char(round((f.sumbytes+d.Extend_bytes)/1024/1024/1024,2)) || 'GB' free_extend,
to_char(round((d.sumbytes-f.sumbytes)/1024/1024/1024,2)) || 'GB' used,
to_char(round((d.sumbytes-f.sumbytes)*100/(d.sumbytes+d.extend_bytes),2)) || '%' as used_extend
from (select tablespace_name,sum(bytes) sumbytes from dba_free_space group by tablespace_name) f,
(select tablespace_name,sum(aa.bytes) sumbytes,sum(aa.extend_bytes) extend_bytes from (select nvl(case
    when autoextensible ='YES' then
    (maxbytes-bytes)    end,0) Extend_bytes
,tablespace_name,bytes  from dba_data_files) aa group by tablespace_name) d
where f.tablespace_name= d.tablespace_name
order by  used_extend desc;
复制代码

可看到 system 表空间已被耗尽：

清空审计日志表sys.aud$（sys管理员用户执行）

truncate table sys.aud$;
复制代码

再次查看表空间可看到 system 表空间已被清理：

4、改进建议：

关闭数据库审计

--查看数据库审计状态，默认开启
show parameter audit      

--关闭数据库审计
alter system set audit_trail=none scope=spfile;    

--查看数据库审计状态，此时关闭参数未生效
show parameter audit   
复制代码

重启数据库生效更改参数

--关闭数据库
shu immediate     

--启动数据库
startup

--重启数据库后参数生效
show parameter audit      
复制代码