As per the docs:
COMPRESS compresses all LOBs in the segment and then returns. NOCOMPRESS uncompresses all LOBs in the segment and then returns. So both happen when you change this property.
First, let's create an uncompress LOB and load it up with highly-compressible data:
create table t1 (
x int,
c clob
) lob (c) store as securefile (
enable storage in row
nocompress
);
insert into t1
select rownum, rpad('b',12000,'b')
from dual
connect by level <= 10000;
commit;
select segment_name, sum ( bytes )
from user_segments
where segment_name = 'T1' or
segment_name = (
select segment_name from user_lobs
where table_name = 'T1'
)
group by rollup ( segment_name );
SEGMENT_NAME SUM(BYTES)
SYS_LOB0000172177C00002$$ 100859904
T1 655360
<null> 101515264
exec show_space;
Segment blocks = 12312 bytes = 100859904
Used blocks = 10000 bytes = 81920000
Expired blocks = 2162 bytes = 17711104
Unexpired blocks = 0 bytes = 0Now when we compress the LOB, what happens?
alter table t1 modify
lob ( c ) ( compress );
select segment_name, sum ( bytes )
from user_segments
where segment_name = 'T1' or
segment_name = (
select segment_name from user_lobs
where table_name = 'T1'
)
group by rollup ( segment_name );
SEGMENT_NAME SUM(BYTES)
SYS_LOB0000172177C00002$$ 100859904
T1 2097152
<null> 102957056 Eh? The segments are still the same size! No compression!
Or are they... What does the space report show?
exec show_space;
Segment blocks = 12312 bytes = 100859904
Used blocks = 0 bytes = 0
Expired blocks = 2094 bytes = 17154048
Unexpired blocks = 10000 bytes = 81920000
Aha! Used blocks (active LOBs) down from 1,000 to
zero. We have compressed the data!
If you move the lob, it creates a new segment. Which is much smaller than before:
alter table t1 move
lob ( c ) store as securefile;
select segment_name, sum ( bytes )
from user_segments
where segment_name = 'T1' or
segment_name = (
select segment_name from user_lobs
where table_name = 'T1'
)
group by rollup ( segment_name );
SEGMENT_NAME SUM(BYTES)
SYS_LOB0000172177C00002$$ 131072
T1 2097152
<null> 2228224
exec show_space;
Segment blocks = 16 bytes = 131072
Used blocks = 0 bytes = 0
Expired blocks = 5 bytes = 40960
Unexpired blocks = 0 bytes = 0Now if you uncompress the LOB, the data won't fit in the existing segments. So they'll grow:
<codde>alter table t1 modify
lob ( c ) ( nocompress );
select segment_name, sum ( bytes )
from user_segments
where segment_name = 'T1' or
segment_name = (
select segment_name from user_lobs
where table_name = 'T1'
)
group by rollup ( segment_name );
SEGMENT_NAME SUM(BYTES)
SYS_LOB0000172177C00002$$ 84017152
T1 2097152
<null> 86114304
exec show_space;
Segment blocks = 10256 bytes = 84017152
Used blocks = 10000 bytes = 81920000
Expired blocks = 99 bytes = 811008
Unexpired blocks = 0 bytes = 0</code>
The show_space procedure is:
create or replace procedure show_space as
l_segment_size_blocks number;
l_segment_size_bytes number;
l_used_blocks number;
l_used_bytes number;
l_expired_blocks number;
l_expired_bytes number;
l_unexpired_blocks number;
l_unexpired_bytes number;
v_segname varchar2(30);
begin
select segment_name
into v_segname
from user_lobs
where table_name = 'T1'
and column_name = 'C';
dbms_space.space_usage(
segment_owner => user,
segment_name => v_segname,
segment_type => 'LOB',
segment_size_blocks => l_segment_size_blocks,
segment_size_bytes => l_segment_size_bytes,
used_blocks => l_used_blocks,
used_bytes => l_used_bytes,
expired_blocks => l_expired_blocks,
expired_bytes => l_expired_bytes,
unexpired_blocks => l_unexpired_blocks,
unexpired_bytes => l_unexpired_bytes
);
dbms_output.put_line(' Segment blocks = '||l_segment_size_blocks||' bytes = '||l_segment_size_bytes);
dbms_output.put_line(' Used blocks = '||l_used_blocks||' bytes = '||l_used_bytes);
dbms_output.put_line(' Expired blocks = '||l_expired_blocks||' bytes = '||l_expired_bytes);
dbms_output.put_line(' Unexpired blocks = '||l_unexpired_blocks||' bytes = '||l_unexpired_bytes);
end show_space;
/