Skip to Main Content
  • Questions
  • How to Reclaim Space After NULLing LOBs

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, George.

Asked: July 26, 2017 - 7:30 am UTC

Last updated: July 26, 2017 - 3:48 pm UTC

Version: 11c

Viewed 10K+ times! This question is

You Asked

On this table that contains BLOBs and up to today filled up a 32GB tablespace, I removed a large percentage of BLOBs by setting them to NULL with an UPDATE statement (leaving the remaining columns untouched of course). No space was actually freed so I had to add a 2nd datafile, 4GB, in order to allow INSERT queries to work. I did a "alter table X move", no benefit. I did a "alter table X shrink space", it actually increased the occupancy of the 2nd datafile and left the 1st one unaffected.

I found a utility on the net ( https://oracle-base.com/dba/script.php?category=monitoring&file=ts_free_space.sql ) that calculates how much space can be saved, and it gives me that the USERS ts has just 4004MB that can be freed, when I'd expect at least 10GB (4GB from the 2nd datafile that should be basically empty, plus at least 6GB from the 1st datafile that was initially filled to capacity=32GB).

It seems impossible to free up space. Was setting to NULL a bad idea? Why is Oracle refusing to see any space as reclaimable?

I can't have a ts that keeps on growing, obviously.... On this particular db I can go ahead and remove those rows that I updated if you think it's going to help, but on other installations I need to just set the BLOB to null and keep the row.

To be more specific, this query:

 select file_name, hwm, blocks total_blocks, blocks-hwm+1 shrinkage_possible
  from dba_data_files a,
         ( select file_id, max(block_id+blocks) hwm
             from dba_extents
            group by file_id ) b
    where a.file_id = b.file_id;


...gives this result (the users*.dbf are the pertinent files, 01 is the old one that was filled up and 02 is the fresh one I created today)

FILE_NAME                                                                 HWM TOTAL_BLOCKS SHRINKAGE_POSSIBLE
------------------------------------------------------------------ ---------- ------------ ------------------
/home/oracle/app/oracle/oradata/orcl/users01.dbf                      4194176      4194240                 65
/home/oracle/app/oracle/oradata/orcl/users02.dbf                       183816       524288             340473
/home/oracle/app/oracle/oradata/orcl/system01.dbf                       89984        90880                897
/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf                       73640        78080               4441
/home/oracle/app/oracle/oradata/orcl/example01.dbf                      10392        12800               2409
/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf                      53376        64640              11265




- George

and Chris said...

The key thing to watch for here is whether your lobs (clob or blob) are inline or out-of-line.

If they're inline, they'll go in the table segment. Otherwise they go in the LOB segment.

To be inline:

The column values must be "small" (less than 4,000 bytes)
The lob is set as "enable storage in row" (the default)

http://docs.oracle.com/database/122/ADLOB/LOB-storage-with-applications.htm#ADLOB45273

If the lobs for all your rows are inline, after you null them you can reclaim the space they used by moving or shrinking the table.

But once a lob is out-of-line, it remains out-of-line. So to reclaim the space you need to move/shrink the lob, not the table!

In the demo below I start out with some small inline lobs. After setting these to null, shrinking the table reclaims the space:

create tablespace lobtest
logging datafile 'lobtest.dbf' size 10m
extent management local
segment space management auto;

create table t ( x clob ) lob (x) store as (enable storage in row)
  enable row movement tablespace lobtest;

insert into t 
  select lpad('x', 1000, 'x') from dual
  connect by level <= 1000;
  
commit;

select segment_name from user_lobs
where  table_name = 'T';

SEGMENT_NAME               
SYS_LOB0000116998C00001$$ 

select segment_name, bytes 
from   user_segments
where  segment_name in ('T', 'SYS_LOB0000116998C00001$$');

SEGMENT_NAME               BYTES    
SYS_LOB0000116998C00001$$  65536    
T                          2097152  

update t 
set    x = null;

commit;

alter table t shrink space;

select segment_name, bytes 
from   user_segments
where  segment_name in ('T', 'SYS_LOB0000116998C00001$$');

SEGMENT_NAME               BYTES  
SYS_LOB0000116998C00001$$  65536  
T                          65536 


But if I whack a single, large clob in it goes in the lob segment. So to get this space back after clearing it out, I need to shrink the lob:

truncate table t;

select segment_name, bytes 
from   user_segments
where  segment_name in ('T', 'SYS_LOB0000116998C00001$$');

SEGMENT_NAME               BYTES  
SYS_LOB0000116998C00001$$  65536  
T                          65536 

declare
  l_clob   clob;
  l_size   number := 10000;
begin
  insert into t values ( empty_clob() ) returning x into l_clob;

  for i in 1..10 loop
    dbms_lob.writeappend(
      l_clob,
      l_size,
      lpad('x', l_size, 'x')
    );

    l_size := 31191;
  end loop;

  commit;
end;
/


select segment_name, bytes 
from   user_segments
where  segment_name in ('T', 'SYS_LOB0000116998C00001$$');

SEGMENT_NAME               BYTES   
SYS_LOB0000116998C00001$$  458752  
T                          65536   

update t
set    x = null;

commit;

alter table t shrink space;

select segment_name, bytes 
from   user_segments
where  segment_name in ('T', 'SYS_LOB0000116998C00001$$');

SEGMENT_NAME               BYTES   
SYS_LOB0000116998C00001$$  458752  
T                          65536 

alter table t modify lob (x) (shrink space);

select segment_name, bytes 
from   user_segments
where  segment_name in ('T', 'SYS_LOB0000116998C00001$$');

SEGMENT_NAME               BYTES  
SYS_LOB0000116998C00001$$  65536  
T                          65536  


I can't have a ts that keeps on growing, obviously....

Really? Aren't databases supposed to grow over time? ;)

Rating

  (1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Many thanks

George Develekos, July 26, 2017 - 3:23 pm UTC

Moments after I found on the net that "modify lob(x)" trick, I received your response that confirmed it.

Thanks for your time, your group is doing a splendid job here.
Chris Saxon
July 26, 2017 - 3:48 pm UTC

Thanks :)

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database