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
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>