绑定完请刷新页面
取消
刷新

分享好友

×
取消 复制
用户delete表数据后如何压缩lob字段以及处理long数据类型带来的压缩报错
2020-11-19 17:26:21

用户反馈删除两个表中的多数数据,发现空间没有将下来,有两个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);

分享好友

分享这个小栈给你的朋友们,一起进步吧。

Oracle运维新鲜事-技术与管理各占半边天
创建时间:2020-08-04 11:34:57
本技术栈旨在分享技术心得,运维趣事,故障处理经验,调优案例,故障处理涉及集群,DG,OGG,大家生产中遇到的问题基本都会囊括了,我会发布生产库遇到的故障,希望在交流中互助互益,共同提高,也希望大家讨论,如果您有生产中遇到的集群问题,也可以在这里提出来,一起讨论,现实中也帮助不少同学解决了生产库的故障。
展开
订阅须知

• 所有用户可根据关注领域订阅专区或所有专区

• 付费订阅:虚拟交易,一经交易不退款;若特殊情况,可3日内客服咨询

• 专区发布评论属默认订阅所评论专区(除付费小栈外)

栈主、嘉宾

查看更多
  • Abraham林老师
    栈主
  • 小雨滴
    嘉宾
  • hawkliu
    嘉宾
  • u_97a59a25246404
    嘉宾

小栈成员

查看更多
  • 栈栈
  • dapan
  • 小菜鸟___
  • hwayw
猜你喜欢
戳我,来吐槽~