Oracle Exadata Hybrid Columnar Compression (EHCC) is a powerful feature that reduces storage usage and improves query performance. However, administrators often need to check compression status, adjust compression levels, import/export compressed tables, and restore them. This article explains all these tasks step by step with examples.

Determining If a Table Is Compressed

For Non-Partitioned Tables

Query the *_TABLES view:

SELECT table_name, compression, compress_for 
FROM user_tables;

Example Output:

👉 If COMPRESSION = ENABLED, the table is compressed.

For Partitioned Tables

Query the *_TAB_PARTITIONS view:

SELECT table_name, partition_name, compression, compress_for 
FROM user_tab_partitions;

Example Output:

Determining Which Rows Are Compressed

Rows may lose compression after updates. Use DBMS_COMPRESSION.GET_COMPRESSION_TYPE:

SELECT DBMS_COMPRESSION.GET_COMPRESSION_TYPE('SCOTT','SALES',ROWID) 
FROM SALES WHERE ROWNUM < 10;

Returns the compression type for each row (e.g., QUERY HIGH, OLTP, NOCOMPRESS).

Changing Compression Levels

Scenario

• Recent sales data → QUERY compression (fast queries).
• Old sales data (>6 months) → ARCHIVE compression (space savings).

Options:

• Partitioned Table:

ALTER TABLE sales MODIFY PARTITION q1_2023 COMPRESS FOR ARCHIVE HIGH;

Non-Partitioned Table:

ALTER TABLE sales MOVE COMPRESS FOR QUERY HIGH ONLINE;

Tablespace Default:

• Online Redefinition (DBMS_REDEFINITION): Allows compression changes while table remains available.

📦 4. Importing and Exporting EHCC Tables

Export:

Import (preserves EHCC):

impdp system/password DIRECTORY=dp_dir DUMPFILE=sales.dmp TABLES=sales

Import as Uncompressed:

impdp system/password DIRECTORY=dp_dir DUMPFILE=sales.dmp TABLES=sales \
TRANSFORM=SEGMENT_ATTRIBUTES:n

Error if importing into non-EHCC tablespace:

ORA-64307: Exadata Hybrid Columnar Compression is not supported for tablespaces on this storage type

Restoring EHCC Tables

If Target Supports EHCC

• Use RMAN to restore directly:

RMAN> RESTORE TABLESPACE sales_tbs;

If Target Does NOT Support EHCC

• Convert to uncompressed:

ALTER TABLE sales MOVE ONLINE NOCOMPRESS;
ALTER TABLE sales MOVE ONLINE NOCOMPRESS PARALLEL;

For partitions:

ALTER TABLE sales MOVE PARTITION q1_2023 NOCOMPRESS ONLINE;

👉 After conversion, optionally apply OLTP or In-Memory compression.

Best Practices

• Regularly check compression status with user_tables and user_tab_partitions.
• Use ARCHIVE compression for old, rarely accessed data.
• Use QUERY compression for frequently queried data.
• Use DBMS_REDEFINITION for online compression changes.
• Always ensure enough disk space before changing compression levels.
• Use TRANSFORM option during import if target doesn’t support EHCC.
• Convert EHCC tables to NOCOMPRESS when restoring to non-Exadata systems.