用户delete表数据后如何压缩lob字段以及处理long数据类型带来的压缩报错

用户反馈删除两个表中的多数数据，发现空间没有将下来，有两个lob段占了100G和70G,需要协助处理，下面是详细分析过程，其中涉及到了ORA-00997错误，下面是详细分析过程。

1 查询表中那些lob段以及大小和存储空间。
SQL> select dl.owner, dl.table_name, dl.column_name, dl.segment_name, dl.tablespace_name,ds.bytes/1024/1024/1024 GB from dba_lobs dl ,dba_segments ds where
dl.segment_name=ds.segment_name and dl.owner='user1' and dl.table_name in ('tab1','tab2');

OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE_NAME GB
---------- -------------------- -------------------- ------------------------------ -------------------- ----------
user1 tab1 TRAINCONTENT SYS_LOB0000170157C00009$$ OPMS 105.15332 <<<<<LOB 段
user1 tab2 LINE SYS_LOB0000170311C00003$$ OPMS 67.0917969 <<<<<LOB 段
user1 tab1 FLAG SYS_LOB0000170157C00013$$ OPMS .000061035


2 b表上索引，后续move后需要重建。
SQL> select index_name,table_name,status from dba_indexes where table_name in ('tab1','tab2');

INDEX_NAME TABLE_NAME STATUS
------------------------------ -------------------- --------
SYS_IL0000170157C00013$$ tab1 VALID
SYS_IL0000170157C00009$$ tab1 VALID
INDEX_SAVEBJH tab2 VALID
SYS_IL0000170311C00003$$ tab2 VALID


3 压缩个表
alter table user1.tab2 move tablespace OPMS ;
alter table user1.tab2 move lob(LINE) store as basicfile(tablespace OPMS);
查大小变化
SQL> select dl.owner, dl.table_name, dl.column_name, dl.segment_name, dl.tablespace_name,ds.bytes/1024/1024/1024 GB from dba_lobs dl ,dba_segments ds where
dl.segment_name=ds.segment_name and dl.owner='user1' and dl.table_name in ('tab1','tab2')

OWNER TABLE_NAME COLUMN_NAME SEGMENT_NAME TABLESPACE GB
---------- -------------------- -------------------- ------------------------------ ---------- ----------
user1 tab1 TRAINCONTENT SYS_LOB0000170157C00009$$ OPMS 105.15332
user1 tab2 LINE SYS_LOB0000170311C00003$$ OPMS 27.9384766 《〈〈减少到27G
user1 tab1 FLAG SYS_LOB0000170157C00013$$ OPMS .000061035

重建索引
alter index user1.INDEX_SAVEBJH rebuild;



4 压缩第二个表
alter table user1.tab1 move tablespace OPMS ;
alter table user1.tab1 move lob(TRAINCONTENT) store as basicfile(tablespace OPMS);

报错有long类型字段
SQL> alter table user1.tab1 move tablespace OPMS ;
alter table user1.tab1 move tablespace OPMS
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype


SQL> alter table user1.tab1 move lob(TRAINCONTENT) store as basicfile(tablespace OPMS);
alter table user1.tab1 move lob(TRAINCONTENT) store as basicfile(tablespace OPMS)
*
ERROR at line 1:
ORA-00997: illegal use of LONG datatype

5 可以考虑使用重定义字段类型
重定义检查失败，缺少主键
SQL> exec dbms_redefinition.can_redef_table('user1','tab1',dbms_redefinition.cons_use_pk);
BEGIN dbms_redefinition.can_redef_table('user1','tab1',dbms_redefinition.cons_use_pk); END;

*
ERROR at line 1:
ORA-12089: cannot online redefine table "user1"."tab1" with no primary key
ORA-06512: at "SYS.DBMS_REDEFINITION", line 143
ORA-06512: at "SYS.DBMS_REDEFINITION", line 1635
ORA-06512: at line 1

6根据用户要求，直接修改，该字段都是null值。
SQL> desc user1.tab1;
Name Null? Type
----------------------------------------- -------- ----------------------------

nnnrr long <<<< 目前类型
XDPLANTYPE NUMBER(1)
TRAINCONTENT BLOB
FLAG BLOB
SQL> alter table user1.tab1 modify nnnrr clob;

Table altered.

SQL> desc user1.tab1;
Name Null? Type
----------------------------------------- -------- ----------------------------

nnnrr CLOB <<<< 修改成功
XDPLANTYPE NUMBER(1)
TRAINCONTENT BLOB
FLAG BLOB

7 再次压缩表
alter table user1.tab1 move tablespace OPMS ;
alter table user1.tab1 move lob(TRAINCONTENT) store as basicfile(tablespace OPMS);

查询压缩效果
SQL> selec dl.owner, dl.table_name, dl.column_name, dl.segment_name, dl.tablespace_name,ds.bytes/1024/1024/1024 GB from dba_lobs dl ,dba_segments ds where
dl.segment_name=ds.segment_name and dl.owner='user1' and dl.table_name in ('tab1','tab2')

重建索引：
alter index user1.INDEX_SAVEBJH rebuild;

也可以使用如下方法，自己选择吧。
直接压缩lob字段。 <<<<<需要跑很久，且该表不能有long字段
alter table user1.tab1 modify lob(TRAINCONTENT) (shrink space);
alter table user1.tab2 modify lob(LINE) (shrink space);