ALTER TABLESPACE COMPRESS
To compress an existing tablespace entirely, we take 2 steps.
Tablespace: DEFAULT COMPRESS
Let's see the current status of the tablespace.
SQL> select def_tab_compression, compress_for from dba_tablespaces where tablespace_name = 'EXAMPLE';
DEF_TAB_ COMPRESS_FOR
-------- ------------------------------
DISABLED
So far, COMPRESS has not enabled in the tablespace. Let's make the attribute enabled.
SQL> alter tablespace example default compress for all operations;
Tablespace altered.
SQL> select def_tab_compression, compress_for from dba_tablespaces where tablespace_name = 'EXAMPLE';
DEF_TAB_ COMPRESS_FOR
-------- ------------------------------
ENABLED ADVANCED
We have enabled the COMPRESS feature of the tablespace, from now on, any new segments in the tablespace automatically have compression attribute by default without explicitly declaring.
That is to say, we only turn on the green light of the behavior in the tablespace, all existing segments inside the tablespace have no change at all.
We thought that we can convert an uncompressed tablespace into a fully compressed one in a command, but no, I'm sorry, compression is basically for segments, not data files, not tablespaces.
Segments: MOVE or REBUILD COMPRESS
So far, no any segment is compressed and modified, we need to compress them and change their attributes at the same time, one by one.
- Table: ALTER TABLE MOVE COMPRESS
- Index: ALTER INDEX REBUILD COMPRESS
Is there any chance that we have uncompressed segments in the tablespace with compress attribute? The answer is positive.
Please note that, the feature of basic compression is included in Enterprise Edition, whereas Oracle Advanced Compression is an Oracle Database option which needs purchase in addition to Enterprise Edition.