oracle数据库服务器磁盘空间满、表空间满无法扩容临时解决办法

一、oracle数据库因系统表空间满无法登陆，提示如下：
ORA-00604: error occurred at recursive SQL level 1ORA-01653: unable to extend table SYSTEMAUD$ by8192 in tablespace SYSTEMORA-02002: error while writing to audit trailORA-00604: error occurred at recursive SQL level 1ORA-01653: unable to extend table SYSTEMAUD$ by8192 in tablespace SYSTEM

现在使用sysdba登陆plsql执行2到6的步骤即可

二、查看SYSAUX和SYSTEM系统表空间使用率大小
SELECT *   FROM (SELECT D.TABLESPACE_NAME,                SPACE || 'M' "SUM_SPACE(M)",                BLOCKS "SUM_BLOCKS",                SPACE - NVL(FREE_SPACE, 0) || 'M' "USED_SPACE(M)",                ROUND((1 - NVL(FREE_SPACE, 0) / SPACE) * 100, 2) || '%' "USED_RATE(%)",                FREE_SPACE || 'M' "FREE_SPACE(M)"           FROM (SELECT TABLESPACE_NAME,                        ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,                        SUM(BLOCKS) BLOCKS                   FROM DBA_DATA_FILES                  GROUP BY TABLESPACE_NAME) D,                (SELECT TABLESPACE_NAME,                        ROUND(SUM(BYTES) / (1024 * 1024), 2) FREE_SPACE                   FROM DBA_FREE_SPACE                  GROUP BY TABLESPACE_NAME) F          WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)         UNION ALL         SELECT D.TABLESPACE_NAME,                SPACE || 'M' "SUM_SPACE(M)",                BLOCKS SUM_BLOCKS,                USED_SPACE || 'M' "USED_SPACE(M)",                ROUND(NVL(USED_SPACE, 0) / SPACE * 100, 2) || '%' "USED_RATE(%)",                NVL(FREE_SPACE, 0) || 'M' "FREE_SPACE(M)"           FROM (SELECT TABLESPACE_NAME,                        ROUND(SUM(BYTES) / (1024 * 1024), 2) SPACE,                        SUM(BLOCKS) BLOCKS                   FROM DBA_TEMP_FILES                  GROUP BY TABLESPACE_NAME) D,                (SELECT TABLESPACE_NAME,                        ROUND(SUM(BYTES_USED) / (1024 * 1024), 2) USED_SPACE,                        ROUND(SUM(BYTES_FREE) / (1024 * 1024), 2) FREE_SPACE                   FROM V$TEMP_SPACE_HEADER                  GROUP BY TABLESPACE_NAME) F          WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME(+)          ORDER BY 1)  WHERE TABLESPACE_NAME IN ('SYSAUX', 'SYSTEM');

三、查询SYSTEM表空间中占用空间最大的表
SELECT segment_name, segment_type, round(bytes/1024/1024, 2) AS "Size_MB" FROM dba_segments WHERE tablespace_name = 'SYSTEM' ORDER BY bytes DESC;

此处截图是清空了system.aud$表数据后的截图
四、查询SYSAUX表空间中占用空间最大的表
SELECT segment_name, segment_type, round(bytes/1024/1024, 2) AS "Size_MB" FROM dba_segments WHERE tablespace_name = 'SYSAUX' ORDER BY bytes DESC;

五、备份 system.aud$表的前100条数据，数据导出为SQL文件
SELECT  *  FROM  system.aud$  WHERE  ROWNUM  <=  100;
六、清空system.aud$表中的登陆日志
TRUNCATE  TABLE  system.aud$;
一般清空登陆日志后会释放一些系统表空间，即可临时登陆。