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

SYSTEM表空间满了导致数据库无法正常访问，处理过程。

1. sqlplus 登录尝试连接到用户报错

SQL> conn
Enter user-name: dbcenter
Enter password:
ERROR:
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.查看表空间使用率

#设置每行字节数
set linesize 1000
#查询表空间使用率
SELECT
    d.tablespace_name   "Name",
    d.status            "Status",
    d.contents          "Type",
    to_char(nvl(a.bytes / 1024 / 1024, 0), '99G999G999D99') "Size (MB)",
    to_char(NVL(f.bytes, 0)/1024/1024,'99G999G999D99')      "Free (MB)",
    to_char(nvl(a.bytes - nvl(f.bytes, 0), 0) / 1024 / 1024, '99G999G999D99') "Used (MB)",
    to_char(nvl((a.bytes - nvl(f.bytes, 0)) / a.bytes * 100, 0), '999D99') "Used%"
FROM
    sys.dba_tablespaces d,
    (
        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
    ) f
WHERE
    d.tablespace_name = a.tablespace_name (+)
    AND d.tablespace_name = f.tablespace_name (+)
 AND NOT (
    d.extent_management like 'LOCAL'
    AND
    d.contents like 'TEMPORARY'
  )
UNION ALL 
SELECT
    d.tablespace_name   "Name",
    d.status            "Status",
    d.contents          "Type",
    to_char(nvl(a.bytes / 1024 / 1024, 0), '99G999G999D99') "Size (MB)",
    to_char(nvl(a.bytes - nvl(t.bytes, 0), 0) / 1024 / 1024, '99G999G999D99') "Free (MB)",
    to_char(nvl(t.bytes, 0) / 1024 / 1024, '99G999G999D99') "Used (MB)",
    to_char(nvl(t.bytes, 0) / a.bytes * 100, '999D99') "Used%"
FROM
    sys.dba_tablespaces d,
    (
        SELECT
            tablespace_name,
            SUM(bytes) bytes
        FROM
            dba_temp_files
        GROUP BY
            tablespace_name
    ) a,
    (
        SELECT
            tablespace_name,
            SUM(bytes_cached) bytes
        FROM
            v$temp_extent_pool
        GROUP BY
            tablespace_name
    ) t
WHERE
    d.tablespace_name = a.tablespace_name (+)
    AND d.tablespace_name = t.tablespace_name (+)
    AND d.extent_management LIKE 'LOCAL'
    AND d.contents LIKE 'TEMPORARY'
ORDER BY 7 desc;

#查看datafile的大小
select t.tablespace_name, t.file_name, t.autoextensible, t.bytes / 1024 / 1024 "SIZE(M)",t.increment_by * 8 / 1024 "NEXT(M)",t.maxbytes / 1024 / 1024 "MAXSIZE(M)" from dba_data_files t;

发现 SYSTEM 表空间剩余大小为0，且只有一个datafile文件

Name	  Status	 Type	   Size (MB)	  Free (MB)  Used (MB)      Used%
--------- --------- --------- -------------- --------------  ------------ -------

SYSTEM	  ONLINE       PERMANENT	32,767.98  .00        32,767.98    100.00

3.调整SYSTEM表空间

ALTER TABLESPACE SYSTEM ADD DATAFILE '/u01/app/oracle/oradata/orcl/system02.dbf' SIZE 500M AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;