Skip to Main Content
  • Questions
  • BLOB file deleted and LOBSEGMENT is still big.

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Thi Huong Giang.

Asked: December 06, 2024 - 10:28 am UTC

Last updated: December 11, 2024 - 6:10 am UTC

Version: 18c

Viewed 100+ times

You Asked

Hi ask Tom team,
at first sorry for my lacked knowledge of DBA.
I have a BLOB Table and BLOB total Length from the beginning ist over 3GB, and ofcourse the TABLESPACE size is more than 3GB
Now I deleted 3GB BLOB Files and only around 30MB left.
But I cannot shrink the Tablespace smaller because LOBSEGMENTS are now extended to Block_ID Position up to 3Gb.
Is there anyway to come over it, to rearrange segments in TABLESPACE like before they were extended

Many thanks

and Connor said...

Freeing up the *data* makes that space available for new data *in that LOB* to use it. It does not free up the space for anything else (other tables etc) to use it.

To do that, you need to MOVE the lob data so that the database will rewrite the (non-empty) blocks and free up the empty blocks back to the tablespace.

alter table t move lob ( c ) store as securefile;

should free up that space, eg

SQL> create table t ( x int, c clob);

Table created.

SQL> insert into t
  2  select rownum, rpad('x',32000,'x')
  3  from dual
  4  connect by level <= 1000;

1000 rows created.

SQL>
SQL> select * from user_lobs
  2  where table_name = 'T'
  3  @pr
==============================
TABLE_NAME                    : T
COLUMN_NAME                   : C
SEGMENT_NAME                  : SYS_LOB0000285969C00002$$
TABLESPACE_NAME               : USERS
INDEX_NAME                    : SYS_IL0000285969C00002$$
CHUNK                         : 8192
PCTVERSION                    :
RETENTION                     :
FREEPOOLS                     :
CACHE                         : NO
LOGGING                       : YES
ENCRYPT                       : NO
COMPRESSION                   : NO
DEDUPLICATION                 : NO
IN_ROW                        : YES
FORMAT                        : ENDIAN NEUTRAL
PARTITIONED                   : NO
SECUREFILE                    : YES
SEGMENT_CREATED               : YES
RETENTION_TYPE                : DEFAULT
RETENTION_VALUE               :
VALUE_BASED                   : NO
MAX_INLINE                    : 4000

PL/SQL procedure successfully completed.

SQL>
SQL> select bytes
  2  from user_segments
  3  where segment_name in ('T','SYS_LOB0000285969C00002$$','SYS_IL0000285969C00002$$');

     BYTES
----------
     65536
  75825152
    131072

SQL> delete from t;

1000 rows deleted.

SQL> commit;

Commit complete.

SQL> select bytes
  2  from user_segments
  3  where segment_name in ('T','SYS_LOB0000285969C00002$$','SYS_IL0000285969C00002$$');

     BYTES
----------
     65536
  75825152
    131072

SQL> alter table t move lob ( c ) store as securefile;

Table altered.

SQL> select bytes
  2  from user_segments
  3  where segment_name in ('T','SYS_LOB0000285969C00002$$','SYS_IL0000285969C00002$$');

     BYTES
----------
     65536
    262144
     65536


Rating

  (1 rating)

Comments

Thanks Connor,

Giang Doan, December 10, 2024 - 8:48 am UTC

Yes, it worked.
Now I can see free space correctly. Free space now is more than 3GB.

After 'doing something' now I can shrink the tablespace.

Many thanks and see you next time with another topic
Connor McDonald
December 11, 2024 - 6:10 am UTC

glad we could help

More to Explore

CLOB/BLOB/etc

Complete documentation on Securefiles and Large Objects here