oracle数据库服务器磁盘空间满、表空间满无法扩容临时解决办法_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$;

一般清空登陆日志后会释放一些系统表空间，即可临时登陆。