如何摆脱空但巨大的 LOB 列? [英] How to get rid of empty but huge LOB column?

问题描述

我们有一个带有 LOB 列的表，该列是空的，因为内容已转移到其他地方(另一列).但是，LOB 段仍然占用 300 GB，我无法摆脱它.

We've got a table with a LOB column which is empty as the content has be transfered to some other place (another column). However, the LOB segment still occupies 300 GB, and I cannot get rid of it.

这是一个最小的例子:

CREATE TABLE t (id NUMBER, c CLOB) 
  LOB (c) STORE AS BASICFILE (DISABLE STORAGE IN ROW RETENTION NONE);
INSERT INTO t SELECT object_id, object_name FROM all_objects;
COMMIT;

UPDATE T SET c=NULL;
COMMIT;

现在，LOB 是空的，但存储仍然占用 500 MB:

Now, the LOB is empty, but the storage still occupies 500 MB:

SELECT s.bytes/1024/1024 as mb 
  FROM user_lobs 
  JOIN dba_segments s using (segment_name);

MB
528

在最小的例子中，ALTER TABLE xxx MOVE 有效，但不适用于真实的表，因为它太大并且会遇到各种 Oracle 错误.在线重新定义也不起作用，因为底层数据类型是 BINARY XML.Expdp/impdp 生产时间过长.

In the minimal example, ALTER TABLE xxx MOVE works, but not with the real table, as it is too big and runs into various Oracle errors. Online redefinition doesn't work, too, as the underlying datatype is BINARY XML. Expdp/impdp takes too long for production.

我简直不敢相信不可能回收完全空列的空间.

I simply can't believe that it is not possible to reclaim the space of a totaly empty column.

谁能帮忙?

推荐答案

作为删除和重新添加列的替代方法，您可以使用 shr​​ink space 子句:

As an alternative to dropping and re-adding the column you could use the shrink space clause:

ALTER TABLE t MODIFY LOB (c) (SHRINK SPACE);

db<>fiddle，这是 18c，但应该在 11g 中工作也.(后来:是的，它在 11gR2 中确实存在，无论如何省略了 retention none；不过 SQL Fiddle 不喜欢它.)

db<>fiddle, which is 18c but ought to work in 11g too. (Later: yes, it does in 11gR2 with retention none omitted anyway; SQL Fiddle doesn't like it though.)

底层数据类型是 BINARY XML

the underlying datatype is BINARY XML

错过了那个细节，但它仍然有效；您只需要一个额外的步骤来查找备份 XMLType 列的隐藏 BLOB 列，如此处所示.我已经使 alter 动态化只是为了即时获取它，但是如果您可以手动找到它，那么您显然可以自己将其插入语句中:

Missed that detail, but it still works; you just need an extra step to find the hidden BLOB column backing up the XMLType column, as shown here. I've made the alter dynamic just to pick that up on the fly, but if you can find it manually then you can just plug it into the statement yourself obviously:

DECLARE
  l_name USER_TAB_COLUMNS.COLUMN_NAME%TYPE;
  l_stmt VARCHAR2(100);
BEGIN
  select column_name
  into l_name
  from user_tab_cols 
  where 
    table_name = 'T' and hidden_column = 'YES'
    and
    column_id = (
        select column_id 
        from user_tab_cols 
        where table_name = 'T' and column_name = 'X'
    );

  l_stmt := 'ALTER TABLE t MODIFY LOB ("' || l_name || '") (SHRINK SPACE)';
  dbms_output.put_line(l_stmt);
  execute immediate l_stmt;
END;
/

db<>fiddle

可能值得注意的是，这适用于 basicfile 存储，如您的最小演示所示，但可能不适用于 securefile 存储 - 至少在某些时候会抛出ORA-10635: 段或表空间类型无效.

Probably worth noting that this works with basicfile storage, as shown in your minimal demo, but might not work with securefile storage - at least some of the time that throws ORA-10635: Invalid segment or tablespace type.

这篇关于如何摆脱空但巨大的 LOB 列?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！