0

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.

6
  • What happened when you tried? Aug 1, 2016 at 8:25
  • I'm yet to. I need to know if that is the right way.
    – tesla747
    Aug 1, 2016 at 8:26
  • @BalazsPapp have edited the question.
    – tesla747
    Aug 1, 2016 at 8:30
  • 1
    Just create a test table with the same config and run the command.
    – Mat
    Aug 1, 2016 at 8:40
  • I don't get why you think you can't. There are completely different things... You are comparing shrinking segments (already existing segments) vs the default tablespace where your segments will be written if you don't specify one.
    – vegatripy
    Aug 1, 2016 at 13:11

1 Answer 1

3

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.