记一次CLOB字段占用USERS表空间解决

1. 问题描述

系统功能保存提示错误信息“unable to extend lob segment SYS_LOB0000120286C00011$$ by 128 in tablespace USERS”；由此信息可知道，数据库中有对象占用了USERS表空间，而USERS表空间为系统表空间，是禁止自动扩展的，此时就需要对对象SYS_LOB0000120286C00011$$进行表空间迁移，放在业务表空间里；

2. 问题解决

首先查询此对象的字段类型，确认是否为LOB字段；

select * from dba_objects where OBJECT_NAME = 'SYS_LOB0000120286C00011$$';

查看执行结果的OBJECT_TYPE是否为LOB；如下图：

第二部，确认此对象所属哪个用户的哪个表的哪个字段，以及确定其当前占用的表空间是哪个；

 select * from dba_lobs where SEGMENT_NAME='SYS_LOB0000120286C00011$$';

执行结果：

OWNER表示其所属哪个用户

TABLE_NAME表示其所属哪个表

COLUNMN_NAME表示其所属哪个字段

TABLESPACE_NAME表示其当前所占用表空间

如果TABLESPACE_NAME结果为USERS那就表示要对这个对象进行表空间迁移了。

在当前用户下（及上述OWNER），执行下述语句，完成迁移

 ALTER TABLE OWNER.TABLE_NAME
  MOVE LOB(COLUNMN_NAME) 
  STORE AS (TABLESPACE jobspace);

OWNER--用户名，上述查询结果的OWNER；

TABLE_NAME--表名，上述查询结果的TABLE_NAME；

COLUNMN_NAME-字段名，上述查询结果的COLUNMN_NAME；

jobspace--需要迁移的业务表空间；

3. 结果验证

查看USERS表空间是否释放：

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  
AND UPPER(F.TABLESPACE_NAME)='USERS'   
ORDER BY 4 DESC;

执行结果

可以看到USERS表空间得到释放；

4.补充内容

4.1更换表所在表空间

ALTER TABLE table_name MOVE TABLESPACE newtablespace;

4.2查看表空间下都有哪些表

select * from dba_tables where TABLESPACE_NAME='USERS';

4.3查看当前用户下各对象的大小

SELECT segment_name,
        round(SUM(bytes) / (1024 * 1024 * 1024), 2) AS "size(G)"
   FROM user_segments
  GROUP BY segment_name
  ORDER BY SUM(bytes) DESC;