How to get rid of empty but huge LOB column?

user_1864029 0
Viewed 594

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.

Here is a minimal example:

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;

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

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.

Who can help, please?

oracle11g oracle sql
1 Answers

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, 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.)

the underlying datatype is BINARY XML

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

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.

user_2663040
Related