oracle 表空间满了排查和解决(ORA-1653: unable to extend table test by 128 in tab)

users表空间异常占满处理

问题描述：

日常查询数据库alert日志，发现报错信息ORA-1653: unable to extend table AXJ_REDIS.USSD_UNREPORT_FAIL by 128 in  tablespace USERS，users表空间已满，无法扩展。

问题分析：

USERS表空间是默认用户表空间，在创建一个用户并没有指定此用户使用表空间时，该用户所有信息都会放入到users表空间中，使用查询表空间语句：select file_name,tablespace_name,bytes/1024/1024 "bytes MB",maxbytes/1024/1024 "maxbytes MB"   from    dba_data_files where tablespace_name='USERS';  查询users表空间，发现已占满 , 使用sql：select t.TABLE_NAME,t.NUM_ROWS from all_tables t where tablespace_name='USERS' order by num_rows desc;
查询使用USERS表空间的表，按行级降序排序，发现多个表使用USERS表空间，存在大量数据导致USER表占满

问题处理：

1、扩展表空间： alter datafile ‘/oracle/oradata/dbaxj/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;
注：数据被删除后（无论是 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)计算该表空间实际占用的空间，先查询数据块大小： select value from v$parameter where name='db_block_size'，咱默认是8192.
(4)计算实际占用磁盘大小： select 65673 * 8 / 1024 from dual;
(5)把数据文件大小resize到比实际占用磁盘大小大一些就行了：
 alter database datafile '/oracle/oradata/dbaxj/users01.dbf' resize 600m;
这样数据文件大小就变小了，节约空间
4、需要使用的表，修改表空间alter table USSD_UNREPORT_FAIL move tablespace new_tablespace，建表时需养成习惯，指定好表空间