oracle-ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM

与数据交流的路上

于 2021-10-14 13:29:47 发布

阅读量7.5k

点赞数 4

分类专栏： oracle 文章标签： oracle 数据库

本文链接：https://blog.csdn.net/line_on_database/article/details/120758149

版权

oracle 专栏收录该内容

2 篇文章

订阅专栏

一、背景

dts服务报错，查看具体报错内容如下

ORA-01653: unable to extend table SYS.AUD$ by 8192 in tablespace SYSTEM

二、解决办法

1、使用sysdba登陆

sqlplus / as sysdba

2、设置每行字节数(超过则换行显示)

set linesize 1000

3、查看表空间情况

select * from (
Select  a.tablespace_name,
        a.bytes/1024/1024 total_bytes,
        b.bytes/1024/1024 free_bytes,
        a.bytes/1024/1024 - b.bytes/1024/1024 use_bytes,
        to_char(trunc((1 - b.bytes/a.bytes)*100)) || '%' use
from         (select tablespace_name,       
                sum(bytes) bytes
           from dba_data_files
          group by tablespace_name) a,
        (select tablespace_name,       
                sum(bytes) bytes
           from dba_free_space
          group by tablespace_name) b
where a.tablespace_name = b.tablespace_name
union all
select         c.tablespace_name,
                c.bytes/1024/1024 total_bytes,
                (c.bytes-d.bytes_used)/1024/1024 free_bytes,
                d.bytes_used/1024/1024 use_bytes,
                to_char(trunc(1 - d.bytes_used/c.bytes)) || '%' use
from                
(select tablespace_name,
                sum(bytes) bytes
from dba_temp_files
group by tablespace_name) c,
(select tablespace_name,
                sum(bytes_used) bytes_used
from v$temp_space_header
group by tablespace_name               
) d
where c.tablespace_name = d.tablespace_name               
)
order by tablespace_name;

4.查看表空间是否有自动扩展能力

SELECT T.TABLESPACE_NAME,D.FILE_NAME,     
D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS     
FROM DBA_TABLESPACES T,DBA_DATA_FILES D     
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME     
 ORDER BY TABLESPACE_NAME,FILE_NAME;

5、查看数据文件路径和数据文件大小

select name from v$datafile;   
# shell里执行，路径写具体已经满的表空间路径
du -sh /.../system04.dbf

6、扩充表空间

# 方法一、增加自动一个扩展的表空间
ALTER TABLESPACE SYSTEM ADD DATAFILE '/../system05.dbf' SIZE 5000M AUTOEXTEND ON NEXT 5000M MAXSIZE 32G;

# 方法二、如果已满的表空间特别小，也可以直接将表空间增大一些
alter database datafile '/.../system05.dbf' resize 40000M;

# 方法三、如果已满的表空间特别小，且没有开启自动增长，也可以使表空间自动增长
# 设置无限制的自动增长
alter database datafile '/userdata/app/Administrators/oradata/ORCL/system01.dbf' autoextend on next 5000M maxsize unlimited;

# 设置有最大限制的自动增长
alter database datafile '/userdata/app/Administrators/oradata/ORCL/system01.dbf' autoextend on next 5000M MAXSIZE 32G;

三、根本原因

系统表空间大是因为开启了审计功能，解决方法如下：

1.truncate掉AUD$表并关闭审计功能：

SQL> alter system set audit_trail=none scope=spfile;
SQL>shutdown immediate;
SQL>startup;

2、将AUD$默认表空间由system移出(慎重操作)

数据库oracle11g默认是关闭审计的，为了跟踪监视用户所执行的数据库操作，审计记录可存在数据字典表（称为审计记录:存储在system表空间中的SYS.AUD $表中，可通过视图dba_audit_trail查看）或操作系统审计记录中(默认位置为$ ORACLE_BASE/admin/$ORACLE_SID/adump/).。默认情况下审计是没有开启的。

如果要开启最好建立别的表空间专门存放审计，这样也不会影响system表空间性能

2.1 新增表空间

create tablespace SHENJI datafile  '/.../shenji01.dbf' size5000MAutoextend onNext 5000M Maxsize  unlimited;

2.2检查审计表现在所在表空间

SQL> SELECT table_name, tablespace_name FROM dba_tables 
WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;

TABLE_NAME           TABLESPACE_NAME
---------------     ----------------------
AUD$                 SYSTEM
FGA_LOG$             SYSTEM

2.3检查审计表的当前数据量

select segment_name,bytes/1024/1024 size_in_mb from dba_segments where segment_name in ('AUD$','FGA_LOG$');

SEGMENT_NAME         SIZE_IN_MB
------------------- -----------------------------
FGA_LOG$             10380

AUD$                 4413

2.4迁移存储表

使用存储过程：DBMS_AUDIT_MGMT.SET_AUDIT_TRAIL_LOCATION

#迁移aud$ 

SQL> BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_AUD_STD,--this moves table AUD$
audit_trail_location_value => 'AUDIT_TBS');
END;
/
PL/SQL procedure successfully completed.

 

#迁移FGA_LOG$表

SQL> BEGIN
DBMS_AUDIT_MGMT.set_audit_trail_location(
audit_trail_type => DBMS_AUDIT_MGMT.AUDIT_TRAIL_FGA_STD,--this moves table FGA_LOG$
audit_trail_location_value => 'AUDIT_TBS');
END;
/
PL/SQL procedure successfully completed.

2.5检查迁移结果

SQL> SELECT table_name, tablespace_name FROM dba_tables
WHERE table_name IN ('AUD$', 'FGA_LOG$') ORDER BY table_name;

TABLE_NAME              TABLESPACE_NAME
----------------------- ------------------------------
AUD$                    AUDIT_TBS
FGA_LOG$                AUDIT_TBS

借鉴大佬文章：
https://www.zhangqiongjie.com/1527.html
https://www.cnblogs.com/lz-2021/p/14875365.html