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('TBX',
'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('TBX',
'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;
/
