Oracle数据库表的数据量巨大,可以达到上亿条,导致继续向表里insert数据时报ora-01654异常,可能原因是数据库表空间不足,另一个原因可能是表的索引空间不足,均可以通过扩展表空间容量解决。关于表空间的扩容如下:
查看表空间利用情况:
SELECT TOTAL.TABLESPACE_NAME AS 表空间名,TOTAL.file_name,
ROUND(TOTAL.MB - FREE.MB, 2) || ' MB' AS 当前已使用的空间,
ROUND(TOTAL.MB, 2) || ' MB' AS 当前可使用总空间,
ROUND(FREE.MB, 2) || ' MB' AS 当前剩余可使用空间,
ROUND((1 - FREE.MB / TOTAL.MB) * 100, 2) || '%' AS 当前使用百分比,
ROUND(TOTAL.MAX_MB, 2) || ' MB' AS 可扩展到的最大空间,
DECODE(TOTAL.MAX_MB, 0, 0, ROUND(TOTAL.MAX_MB - TOTAL.MB, 2)) ||
' MB' AS 剩余可扩展的空间,
DECODE(TOTAL.MAX_MB,
0,
0,
ROUND((1 - TOTAL.MB / TOTAL.MAX_MB) * 100, 2)) || '%' AS 剩余可扩展的百分比
FROM (SELECT TABLESPACE_NAME,SUM(BYTES) / 1024 / 1024 AS MB
FROM DBA_FREE_SPACE
GROUP BY TABLESPACE_NAME) FREE,
(SELECT TABLESPACE_NAME,file_name,
SUM(BYTES) / 1024 / 1024 AS MB,
SUM(MAXBYTES) / 1024 / 1024 AS MAX_MB
FROM DBA_DATA_FILES
GROUP BY TABLESPACE_NAME,file_name) TOTAL
WHERE FREE.TABLESPACE_NAME = TOTAL.TABLESPACE_NAME
ORDER BY TOTAL.TABLESPACE_NAME
第一种方案:直接扩容表空间对应的dbf文件;
alter database datafile '表空间位置' resize 新的尺寸
alter database datafile 'C:\APP\ADMINISTRATOR\ORADATA\XYSHARE\SGSHARE.DBF' resize 3072000m
第二种方案:增加表空间dbf数据文件;
ALTER TABLESPACE app_data ADD DATAFILE D:\ORACLE\PRODUCT\10.2.0\ORADATA\EDWTEST\APP03.DBF' SIZE 500M;