is my data compressed?

All we need is an easy explanation of the problem, so here it is.

Lets say I want to implement basic compression for my table. I know it can be done in two steps, for example:

  1. alter table MYTABLE compress;
  2. alter table MYTABLE move;

Is there a way to check that both steps were implemented? How do I know that data was compressed?

I’m using Oracle 19c if that matters.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

SQL> create table t1 as select * from dba_objects;

Table created.

SQL> select dbms_compression.get_compression_type(user, 'T1', rowid) as compression_type,
     count(*)
     from t1 group by dbms_compression.get_compression_type(user, 'T1', rowid);

COMPRESSION_TYPE   COUNT(*)
---------------- ----------
               1      23861

SQL> alter table t1 compress;

Table altered.

SQL> select dbms_compression.get_compression_type(user, 'T1', rowid) as compression_type,
     count(*)
     from t1 group by dbms_compression.get_compression_type(user, 'T1', rowid);

COMPRESSION_TYPE   COUNT(*)
---------------- ----------
               1      23861

Finally:

SQL> alter table t1 move;

Table altered.

SQL> select dbms_compression.get_compression_type(user, 'T1', rowid) as compression_type,
     count(*)
     from t1 group by dbms_compression.get_compression_type(user, 'T1', rowid);

COMPRESSION_TYPE   COUNT(*)
---------------- ----------
            4096      23861

Table 38-1 DBMS_COMPRESSION Constants – Compression Types

1 - No compression
...
4096 - Basic table compression

Method 2

You can run the DBMS_COMPRESSION.GET_COMPRESSION_RATIO system stored procedure to obtain the actual compression ratio for the table. It won’t tell you whether someone has run alter table ... move, obviously, but it will tell you if the table is actually compressed or only configured.

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply