（ORA-01653）-表空间可用空间不足
现象描述
SQL*Plus: Release 11.1.0.7.0 - Production on Thu Jan 27 12:16:27 2011 Copyright (c) 1982, 2008, Oracle. All rights reserved. ERROR: ORA-00604: error occurred at recursive SQL level 1ORA-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复制
定位思路
检查SYSTEM表空间可用空间大小。
SELECT UPPER(F.TABLESPACE_NAME) "Tablespace Name", D.TOT_GROOTTE_MB "Tablespace Size (M)", D.TOT_GROOTTE_MB - F.TOTAL_BYTES "Used Space (M)", TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') "Usage Ratio", F.TOTAL_BYTES "Free Space (M)", F.MAX_BYTES "Maximum Bytes (M)" FROM (SELECT TABLESPACE_NAME, ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES, ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES FROM SYS.DBA_FREE_SPACE GROUP BY TABLESPACE_NAME) F, (SELECT DD.TABLESPACE_NAME, ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB FROM SYS.DBA_DATA_FILES DD GROUP BY DD.TABLESPACE_NAME) D WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME ORDER BY 4 DESC;
Tablespace Name Tablespace Size(M) Used Space(M) Usage Ratio Free Space(M) Maximum Bytes(M) ---------------- ----------- ----------- ------ ---------- --------- SYSTEM 710 707.31 99.62 2.69 2 SYSAUX 640 597.44 93.35 42.56 32 USERS 5 1.37 27.40 3.63 3.63如上所示，SYSTEM表空间可用空间不足，只有2MB。复制
处理步骤
- 检查表空间的路径。
SQL> select * from dba_data_files;
FILE_NAME -------------------------------------------------------------------------------- FILE_ID TABLESPACE_NAME BYTES BLOCKS STATUS ---------- ------------------------------ ---------- ---------- --------- RELATIVE_FNO AUT MAXBYTES MAXBLOCKS INCREMENT_BY USER_BYTES USER_BLOCKS ------------ --- ---------- ---------- ------------ ---------- ----------- ONLINE_ ------- /dev/vx/rdsk/vgora/lv_system 1 SYSTEM 744488960 90880 AVAILABLE 1 YES 2147483648 262144 1280 743440384 90752 SYSTEM如上所示，“/dev/vx/rdsk/vgora/lv_system”为表空间文件路径。复制 - 增加SYSTEM表空间的大小。
SQL> alter database datafile '表空间文件路径' resize 3000M;
如果问题没有解决，请联系华为技术工程师。
