is my data compressed?

[amarantha]

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.

[Burma]

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

https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/DBMS_COMPRESSION.html#GUID-8C9942CA-4EBD-48FF-9E8C-A59BF21A0176__BEIBFCCB

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