Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, Krishna.

Asked: September 07, 2016 - 10:28 am UTC

Last updated: August 16, 2017 - 11:58 am UTC

Version: 12c

Viewed 10K+ times! This question is

You Asked

Hi,

We have reached 90% of our tablespace (disk space) full, and we thought to compress the tables so that we may free up some space.

select 
segment_name,
segment_type,
bytes/1024/1024 MB
from user_segments 
where segment_type='TABLE' 
and segment_name='DV_XXXXXX_HST';  --- 4341 (Size in MB before COMPRESSION)  


Then we compressed the table for OLTP using

ALTER TABLE DV_XXXXXX_HST MOVE COMPRESS FOR OLTP; 


checking the size of the table after compression

select 
segment_name,
segment_type,
bytes/1024/1024 MB
from user_segments 
where segment_type='TABLE' 
and segment_name='DV_XXXXXX_HST';  --- 1856(Size in MB after COMPRESSION)  


But the no of blocks does not change even after compression as per the below query
The result are exactly the same for before and after compression
 with tabspace as
(
  select
  t.*,
  round( ((t.num_rows*t.avg_row_len) / (1024*1024) ),2) as net_MB,
  round( ((t.blocks*32)/1024),2) as blocks_MB
  from dba_tables t
)
select
  table_name,
  partitioned,
  compression,
  compress_for,
  blocks,
  num_rows,
  net_MB,
  blocks_MB
from tabspace
where
  table_name like '%DV_XXXXXX_HST'   
;

TABLE_NAME,     PARTITIONED,   COMPRESSION,  COMPRESS_FOR,  BLOCKS,   NUM_ROWS,     NET_MB,        BLOCKS_MB
DV_XXXXXX_HST   NO         ENABLED        ADVANCED     128500    9485188     1058.36     4015.63





Please help us understand why the number of blocks for the table has not come down after compression.

Or, Are we looking at it in a wrong perspective ?

Can we achieve it using any other means ? (using DBMS_REDEFINITION not sure...)
How can we reclaim unused free space from the table into table space?

Thank you ,
Krishna

and Connor said...

Compress = compress data *only* during direct mode operations (sqlldr, ctas, insert-append, alter table move). All subsequent (aka normal) DML will not create compressed data.

Compress for oltp = as above, but ALSO for regular dml activities. It doesn't compress *every* time you (for example) add a row, but when the block 'fullness' crosses certain thresholds. Hence you wont see "extra" compression - the table will just stay compressed better during normal dml activity.

And you need a *license* for 'compress for oltp'.

Example below


SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table T as
  2  select * from dba_Objects
  3  order by owner, object_type;

Table created.

SQL>
SQL> select bytes from dba_segments
  2  where segment_name = 'T'
  3  and owner = user;

     BYTES
----------
  13631488

1 row selected.

SQL>
SQL> alter table T move compress;

Table altered.

SQL>
SQL> select bytes from dba_segments
  2  where segment_name = 'T'
  3  and owner = user;

     BYTES
----------
   4194304

1 row selected.

SQL>
SQL> drop table t purge;

Table dropped.

SQL>
SQL> create table T as
  2  select * from dba_Objects
  3  order by owner, object_type;

Table created.

SQL>
SQL> select bytes from dba_segments
  2  where segment_name = 'T'
  3  and owner = user;

     BYTES
----------
  13631488

1 row selected.

SQL>
SQL> alter table T move compress for oltp;

Table altered.

SQL>
SQL> select bytes from dba_segments
  2  where segment_name = 'T'
  3  and owner = user;

     BYTES
----------
   5242880

1 row selected.

SQL>
SQL>


Rating

  (2 ratings)

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

Comments

Statistics?

Jeff, September 07, 2016 - 5:01 pm UTC

Try gathering statistics on your table.

Your first query, on USER_SEGMENTS, shows the table shrank from 4341MB before compression to 1856MB after.

Your second query that shows no change is based on DBA_TABLES, which is only updated when you gather stats on the object/
Chris Saxon
September 08, 2016 - 5:45 am UTC

good point

sai palla, August 14, 2017 - 1:13 pm UTC

In the above explanation can you mention why was the blocks space not got released even though the table is compressed what could be done to release the blocks
Connor McDonald
August 16, 2017 - 11:58 am UTC

Sorry - not sure what you mean. In our demo, the total bytes in user_segments went down, which means the space *was* freed.

More to Explore

DBMS_REDEFINITION

More on PL/SQL routine DBMS_REDEFINITION here