ORA-1653: unable to extend table by 1024 in tablespace(oracle表空间满了的解决方案)

小灰灰 2023-05-31 06:50 10阅读 0赞

## 前言 ##

今天某项目的一个Repository的正常CRUD操作,发现报错信息`ORA-1653:unable to extend table by 1024 in tablespace`,什么意思呢,就是`表空间已满,无法扩展`.

## 问题分析 ##

USERS表空间是默认用户表空间,在创建一个用户并没有指定此用户使用表空间时,该用户所有信息都会放入到users表空间中,如果有指定则一般是用户名相关的表空.

--查看表空间文件
    select file_name t from dba_data_files t where t.tablespace_name='xxxx';
    
    --查看表记录大小
    select t.tablespace_name,t.TABLE_NAME,t.NUM_ROWS from all_tables t where t.tablespace_name='xxxx' order by num_rows desc;
    
    --查看表空间大小
    select t.file_name,t.tablespace_name,t.bytes/1024/1024 "bytes MB",t.maxbytes/1024/1024 "maxbytes MB" from t.dba_data_files where tablespace_name='xxx';

查询使用xxx表空间的表,按行级降序排序,一般多个表使用相同表空间,存在大量数据导致USER表占满,像刚才查看的这个问题的表,超过一千万条记录.

## 解决方案 ##

1.  扩展表空间: `alter datafile '/oracle/oradata/dba/users01.dbf' resize 30G;`
2.  扩展到最大30G文件无法继续扩展,可增加数据文件：`alter tablespace users add datafile 'users02.dbf' size 1024m autoextend on next 1024m maxsize 30G;`
3.  truncate删除无用表释放空间,假如未释放,对TEST表进行收缩shrink,执行下面三个语句:

-- 启用行迁移：
    alter table  TEST enable row movement;
    -- shrink表test：
    alter TABLE  TEST shrink SPACE;
    -- 关闭行迁移：
    alter table  TEST DISABLE row movement;

## Oracle “高水位” ##

数据被删除后(无论是 delete 还是 truncate table),数据文件大小不一定会缩小, 是`Oracle"高水位"所致`,想要降低数据文件大小需降低高水位的正确做法是先降低HWM,再确定实际占有大小,再resize数据文件,执行如下4个语句:

1.  查询表空间文件编号：`select file#, name from v$datafile;`
2.  根据文件 ID 查询这个数据文件最大数据块(data block)的编号：`select max(block_id) from dba_extents where file_id=4;`
3.  计算该表空间实际占用的空间,先查询数据块大小,默认是8192： `select value from v$parameter where name='db_block_size'`
4.  计算实际占用磁盘大小： `select 65673*8/1024 from dual;`
5.  把数据文件大小resize到比实际占用磁盘大小大一些就行了,这样数据文件大小就变小了,节约空间 :`alter database datafile '/oracle/oradata/dba/users01.dbf' resize 600m;`
6.  需要使用的表,修改表空间`alter table xxx move tablespace new_tablespace`,建表时需养成习惯,指定好表空间.

更多详情可以参考 [<<Oracle官方Changing Datafile Size>>][Oracle_Changing Datafile Size]

[Oracle_Changing Datafile Size]: https://docs.oracle.com/cd/B28359_01/server.111/b28310/dfiles003.htm#ADMIN11423