您的位置: 首页  >  文章  >  【ORACLE】ORA-01653: unable to extend table XXXX by 128 in tablespace XXX

【ORACLE】ORA-01653: unable to extend table XXXX by 128 in tablespace XXX

分类: 文章 • 2023-11-16 20:59:22

ORA-01653: unable to extend table XXXX by 128 in tablespace XXX
一次运行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;

返回结果：
【ORACLE】ORA-01653: unable to extend table XXXX by 128 in tablespace XXX
可以看到有很多使用率很大了。知道了空间占用率之后，我们就确定了哪个空间需要扩容了，但是我们还不知道表空间的文件存储在哪？

表空间存储地址

SELECT T.TABLESPACE_NAME,D.FILE_NAME,
D.AUTOEXTENSIBLE,D.BYTES,D.MAXBYTES,D.STATUS
FROM DBA_TABLESPACES T,DBA_DATA_FILES D
WHERE T.TABLESPACE_NAME =D.TABLESPACE_NAME
ORDER BY TABLESPACE_NAME,FILE_NAME;

【ORACLE】ORA-01653: unable to extend table XXXX by 128 in tablespace XXX

这里我们就找到了文件地址了，接下来我们就可以扩容了。

扩容方式

  1. 手工改变已存在数据文件的大小
    alter DATABASE DATAFILE '/oracle/ora11/oradata/ora11g/task.dbf' RESIZE 20480m
  2. 允许已存在的数据文件自动增长
    ALTER DATABASE DATAFILE '/oracle/ora11/oradata/ora11g/task.dbf' AUTOEXTEND ON NEXT 100M MAXSIZE 20480M;
  3. 给表空间增加数据文件
    ALTER TABLESPACE USERS ADD DATAFILE '/oracle/ora11/oradata/ora11g/users02.dbf' size 32000M
    如果想让表空间自动增长需要添加 autoextend on 即：
    ALTER TABLESPACE USERS ADD DATAFILE '/oracle/ora11/oradata/ora11g/users02.dbf' size 32000M AUTOEXTEND ON
    一般我们在创建表空间的时候就可以添加自动增长
    create bigfile tablespace USERS datafile '/oracle/ora11/oradata/ora11g/users.dbf' size 10240m autoextend on next 512m maxsize 20480m extent management local;
  4. 设置为无限大
    alter DATABASE DATAFILE '/oracle/ora11/oradata/ora11g/task.dbf autoextend on maxsize unlimited·

注意表空间大小限制

表空间数据文件的容量与DB_BLOCK_SIZE有关，在初始建库时，DB_BLOCK_SIZE要根据实际需要，设置为4k,8k,16k,32k,64k等几种大小，ORACLE的物理文件大小只允许4194304个数据块（由操作系统决定）即：
4k——16384M
8k——32768M
16k——65536M
32k——131072M
64k——262144M
所以讲maxsize设置为unlimited也不是可以无限扩大，还受限于数据块大小，即blocksize大小，一般blocksize默认8k，所以数据文件大小最大也只能扩展到32G。

当然也可以设置为大文件表空间（bigfile tablespace），8k的bigfile表空间文件理论上最大可以扩展到32TB，但是实际上受操作系统的文件系统限制。使用大文件表空间能够大幅度增加数据库的存储能力，简化数据库管理工作，但是会增加备份和恢复的时间。如果是中小型项目建议使用小文件表空间（smallfile tablespace） 最多可以包含1022个数据块。

相关推荐