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
https://i-blog.csdnimg.cn/blog_migrate/ceee8efcf4134baf1cf1f9da3ddfd944.png
如今使用sysdba登陆plsql实行2到6的步调即可
https://i-blog.csdnimg.cn/blog_migrate/f84c2be9f8e5d5d8d78f557918385402.png
二、查察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');
https://i-blog.csdnimg.cn/blog_migrate/2c06b5acae5a48de8f959ae50b7e7ef6.png
三、查询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;
https://i-blog.csdnimg.cn/blog_migrate/b326df15b24ef62c7e8582609c485e32.png
此处截图是清空了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;
https://i-blog.csdnimg.cn/blog_migrate/53d9139c86e6e33f31f347ad021b0181.png
五、备份 system.aud$表的前100条数据，数据导出为SQL文件
SELECT * FROM system.aud$ WHERE ROWNUM <= 100;
六、清空system.aud$表中的登陆日志
TRUNCATE TABLE system.aud$;
一般清空登陆日志后会释放一些系统表空间，即可临时登陆。
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。
页:
[1]