Query on Shrinking Table – Oracle

bloboracleoracle-12c

I'm trying to shrink a LOB whose owner is TEST and the tablespace it resides is on USERS.

EDIT: Currently, the default tablespace for TEST schema is tablespace called TESTTBL.

I have enabled row movement on the table.

If I try to shrink the particular LOB using: 

ALTER TABLE test MODIFY LOB (image) (SHRINK SPACE);

…will it shrink and release space, becuase the schema TEST has default tablespace TESTTBL and data resides in USERS.

Best Answer

I assume you have a Basicfile LOB, because shrink is not supported with Securefile LOBs. The segment is not being moved, so the default tablespace and the tablespace of the segment is not relevant now. So a simple test is:

SQL> create table t1(c1 clob) lob (c1) store as basicfile;

Table created.

SQL> insert into t1 select lpad('X', 4000) from dual connect by level<=10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL> select bytes/1024/1024 from user_segments where segment_name = 
     (select segment_name from user_lobs where table_name = 'T1' and column_name = 'C1');

BYTES/1024/1024
---------------
             80

SQL> alter table t1 enable row movement;

Table altered.

SQL> delete from t1 where rownum <= 5000;

5000 rows deleted.

SQL> commit;

Commit complete.

SQL> alter table t1 modify lob (c1) (shrink space);

Table altered.

And the LOB segment became half the size after the shrink operation:

SQL> select bytes/1024/1024 from user_segments where segment_name = 
     (select segment_name from user_lobs where table_name = 'T1' and column_name = 'C1');

BYTES/1024/1024
---------------
        39.8125
Related Question