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
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? ;)