【Oracle】ORA-01653的应对处置--增加数据文件
今天在创建一张大表时遭遇到了如下错误：
ORA-01653: 表 LUNA.EMP615_30 无法通过 1024 (在表空间 USERS 中) 扩展
通关查询资料并测试，发现如下三步骤可以应对此错误：
1.查看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
执行情况：
SQL> SELECT UPPER(F.TABLESPACE_NAME) "表空间名", 2 D.TOT_GROOTTE_MB "表空间大小(M)", 3 D.TOT_GROOTTE_MB - F.TOTAL_BYTES "已使用空间(M)", 4 TO_CHAR(ROUND((D.TOT_GROOTTE_MB - F.TOTAL_BYTES) / D.TOT_GROOTTE_MB * 100,2),'990.99') || '%' "使用比", 5 F.TOTAL_BYTES "空闲空间(M)", 6 F.MAX_BYTES "最大块(M)" 7 FROM (SELECT TABLESPACE_NAME, 8 ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES, 9 ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES 10 FROM SYS.DBA_FREE_SPACE 11 GROUP BY TABLESPACE_NAME) F, 12 (SELECT DD.TABLESPACE_NAME, 13 ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB 14 FROM SYS.DBA_DATA_FILES DD 15 GROUP BY DD.TABLESPACE_NAME) D 16 WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME; 表空间名 表空间大小(M) 已使用空间(M) 使用比 空闲空间(M) ------------------------------ ------------- ------------- -------- ----------- 最大块(M) ---------- SYSAUX 660 595.44 90.22% 64.56 34 UNDOTBS1 3185 37.25 1.17% 3147.75 2678 USERS 32761.25 32759.69 100.00% 1.56 1.25 表空间名 表空间大小(M) 已使用空间(M) 使用比 空闲空间(M) ------------------------------ ------------- ------------- -------- ----------- 最大块(M) ---------- SYSTEM 710 705.81 99.41% 4.19 4 EXAMPLE 100 78.75 78.75% 21.25 18.81
2.查看“USERS”表空间所使用数据文件：
SQL：
select file_name,tablespace_name,bytes/1024/1024,maxbytes/1024/1024 from dba_data_files where tablespace_name='USERS';
反馈：
SQL> select file_name,tablespace_name,bytes/1024/1024,maxbytes/1024/1024 from dba_data_files where tablespace_name='USERS'; FILE_NAME -------------------------------------------------------------------------------- TABLESPACE_NAME BYTES/1024/1024 MAXBYTES/1024/1024 ------------------------------ --------------- ------------------ C:\APP\UFO\ORADATA\ORCL\USERS01.DBF USERS 32761.25 32767.9844
3.增加数据文件
SQL：
ALTER TABLESPACE USERS ADD DATAFILE 'C:\app\ufo\oradata\orcl\users001.dbf' size 30G autoextend on ;
反馈：
SQL> ALTER TABLESPACE USERS ADD DATAFILE 'C:\app\ufo\oradata\orcl\users001.dbf' size 30G autoextend on ; 表空间已更改。
增加数据文件到此结束。
【创建大表前后的表空间对比】
创建千万级大表前：
表空间名 表空间大小(M) 已使用空间(M) 使用比 空闲空间(M) ------------------------------ ------------- ------------- -------- ----------- 最大块(M) ---------- SYSAUX 660 596.06 90.31% 63.94 34 UNDOTBS1 3185 37.31 1.17% 3147.69 2699 USERS 63481.25 39817.87 62.72% 23663.38 3968 表空间名 表空间大小(M) 已使用空间(M) 使用比 空闲空间(M) ------------------------------ ------------- ------------- -------- ----------- 最大块(M) ---------- SYSTEM 31430 706.81 2.25% 30723.19 3968 EXAMPLE 100 78.75 78.75% 21.25
之后：
表空间名 表空间大小(M) 已使用空间(M) 使用比 空闲空间(M) ------------------------------ ------------- ------------- -------- ----------- 最大块(M) ---------- SYSAUX 660 596.5 90.38% 63.5 34 UNDOTBS1 3185 499.25 15.68% 2685.75 2495 USERS 63481.25 48486.87 76.38% 14994.38 3968 表空间名 表空间大小(M) 已使用空间(M) 使用比 空闲空间(M) ------------------------------ ------------- ------------- -------- ----------- 最大块(M) ---------- SYSTEM 31430 706.81 2.25% 30723.19 3968 EXAMPLE 100 78.75 78.75% 21.25
从62%到76%，可见此表所占份额。
【参考资料】
https://blog.csdn.net/zcb_data/article/details/80999641
END
