ORA-01653报错解决方法（表空间使用率过高处理）

创建oracle表时遇见以下报错：

ORA-01653: unable to extend table JT_AUDIT.CFG_AUSYS_AUDIT_PROC by 128 in tablespace AUDIT_TABLESPACE

从报错信息来看，应该是oracle表空间不足导致的， 首先检查一下oracle表空间（以下sql可直接复制执行）：

SELECT UPPER(F.TABLESPACE_NAME) "表空间名",
D.TOT_GROOTTE_MB "表空间大小(M)",
D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)",
TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比",
F.TOTAL_BYTES "空闲空间(M)",
F.MAX_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 ;

执行结果：

从结果来看“AUDIT_TABLESPACE”表空间使用率已达到100%，导致该表空间下无法创建新表，以下从两个角度来进行解决表空间过高。

 

1，表空间扩容

查看“AUDIT_TABLESPACE”表空间所使用数据文件：

select file_name,tablespace_name,bytes/1024/1024,maxbytes/1024/1024 from dba_data_files where tablespace_name='AUDIT_TABLESPACE';

查询结果：

增加数据文件（本文采用增加数据文件的方式扩容，也可根据实际选择更改数据文件大小或开启自动增长，建议使用增加数据文件的方式）：

ALTER TABLESPACE AUDIT_TABLESPACE ADD DATAFILE '/data/oradata/dwca/AUDIT_TABLESPACE7.dbf' size 30G autoextend off ;   

---注：一个数据文件最大只能32G

2，清理表

查询对应表空间下最占空间的20个对象：

Select OWNER,SEGMENT_NAME,SEGMENT_TYPE,total||'M' from 
(select OWNER,SEGMENT_NAME,SEGMENT_TYPE,bytes/1024/1024 total from dba_segments 
where TABLESPACE_NAME='AUDIT_TABLESPACE' order by bytes/1024/1024 desc ) where rownum <21;
AUDIT_TABLESPACE' order by bytes/1024/1024 desc ) where rownum <21;

查询结果：

挨个表查询该表是否为必要表，数据是否异常，总能清理出大量的无用表。

删除表的同时不放入回收站，否则达不到清理表空间的目的：

  drop table tablename purge

小记

因为挨个表进行核对清理的过程比较慢，所以大家可以先采用第一种方式，先扩容表空间，再进行挨个大表清理，清理完毕后的表空间容量如下（sql如上：检查oracle表空间sql）：

从检查结果可以看到“AUDIT_TABLESPACE”表空间使用率已经降低到67.71%。