11g Compression and Segment Size

An easy way to observer the compresson in action is to query the user_segments view.

select segment_name,sum(bytes) 
from user_extents where segment_type='TABLE' 
group by segment_name order by 2 desc;

SEGMENT_NAME                   SUM(BYTES)
------------------------------ ----------
AIRLINE                            196608

Compress the table with

alter table airline move compress for all operations;

But the reduction is always not visible in the user_segments view as the segment is not shrunk beyond the initial storage parameter.

select segment_name,sum(bytes) 
from user_extents where segment_type='TABLE' 
group by segment_name order by 2 desc;

SEGMENT_NAME                   SUM(BYTES)
------------------------------ ----------
AIRLINE                            196608

It could be observed that compression is being enabled

select table_name,compression,compress_for from user_tables;

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------
AIRLINE                        ENABLED  FOR ALL OPERATIONS

Looking at the storage parameters

... STORAGE(INITIAL 196608 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL KEEP)

But the actual compression could be observed with the dbms_space package.

Before compression

full_blocks 3
full_bytes 24576

total_blocks 24
total_bytes 196608
unused_blocks 18
unused_bytes 147456
last_used_extent_file_id 6
last_used_extent_block_id 1544

After compression

full_blocks 2
full_bytes 16384

total_blocks 24
total_bytes 196608
unused_blocks 19
unused_bytes 155648
last_used_extent_file_id 6
last_used_extent_block_id 128

There's a reduction in the total number of blocks and increase in the number of unused blocks.

The PL/SQL code is listed below

DECLARE
  unformatted_blocks  NUMBER;
  unformatted_bytes  NUMBER;
  fs1_blocks  NUMBER;
  fs1_bytes   NUMBER;
  fs2_blocks  NUMBER;
  fs2_bytes   NUMBER;
  fs3_blocks  NUMBER;
  fs3_bytes   NUMBER;
  fs4_blocks  NUMBER;
  fs4_bytes   NUMBER;
  full_blocks NUMBER;
  full_bytes  NUMBER;
  
  total_blocks  NUMBER;
  total_bytes  NUMBER;
  unused_blocks  NUMBER;
  unused_bytes  NUMBER;
  last_used_extent_file_id  NUMBER;
  last_used_extent_block_id  NUMBER;
  last_used_block  NUMBER;

--  cursor t is select table_name from user_tables order by 1;
--  tname user_tables.table_name%type;
BEGIN
--open t;
--loop
--fetch t into tname;
--exit when t%notfound;

  DBMS_SPACE.SPACE_USAGE('TX',
                         'AIRLINE', 
                         'TABLE', 
                         unformatted_blocks, 
                         unformatted_bytes, 
                         fs1_blocks, 
                         fs1_bytes, 
                         fs2_blocks, 
                         fs2_bytes, 
                         fs3_blocks, 
                         fs3_bytes, 
                         fs4_blocks, 
                         fs4_bytes, 
                         full_blocks, 
                         full_bytes, 
                         NULL);

--  dbms_output.put_line('table name '|| tname);
--  dbms_output.put_line('fs1_blocks ' || fs1_blocks );
--  dbms_output.put_line('fs1_bytes ' || fs1_bytes  );
--  dbms_output.put_line('fs2_blocks '||fs2_blocks );
--  dbms_output.put_line('fs2_bytes  '||fs2_bytes  );
--  dbms_output.put_line('fs3_blocks  '||fs3_blocks );
--  dbms_output.put_line('fs3_bytes '|| fs3_bytes  );
--  dbms_output.put_line('fs4_blocks '|| fs4_blocks );
--  dbms_output.put_line('fs4_bytes '|| fs4_bytes  );
  dbms_output.put_line('full_blocks '|| full_blocks);
  dbms_output.put_line('full_bytes '|| full_bytes );
  dbms_output.put_line('');
--end loop;
--close t;

  dbms_space.unused_space('TX',
                         'AIRLINE', 
                         'TABLE', 
                          total_blocks ,
                          total_bytes ,
                          unused_blocks ,
                          unused_bytes ,
                          last_used_extent_file_id ,
                          last_used_extent_block_id ,
                          last_used_block , 
                          NULL);

  dbms_output.put_line('total_blocks '|| total_blocks);
  dbms_output.put_line('total_bytes '|| total_bytes );
  dbms_output.put_line('unused_blocks '|| unused_blocks);
  dbms_output.put_line('unused_bytes '|| unused_bytes );
  dbms_output.put_line('last_used_extent_file_id '|| last_used_extent_file_id);
  dbms_output.put_line('last_used_extent_block_id '|| last_used_extent_block_id );
  dbms_output.put_line('');                          


end;
/