CODEWITHSUNDEEP
oracle-databaseplsqlblob
user1815169
user1815169

Reputation: 63

How do I free up space previously assigned to BLOBs?

I've been asked to free up the space assigned to BLOBS in Oracle. I will be extracting the data and then setting the BLOB to null to free up space.

According to the DBA this does not free up the space. SQL I am using is

UPDATE TABLENAME SET BLOB_FIELD_A = NULL

I did some googling and there is a function called EMPTY_BLOB(); No comment anywhere that it frees up the previously allocated space

Upvotes: 1

Views: 1392

Answers (1)

DazzaL
DazzaL

Reputation: 21973

the only way to shrink a segment in oracle is to MOVE/SHRINK it.

ie you have to run, once you've updated the lobs to null:

alter table b modify lob (c) (shrink space);

(available from 10.2 onwards) or

alter table b move lob(c) store as (tablespace your_tablespace);

Upvotes: 3

Related Questions