Oracle Exadata Hybrid Columnar Compression (HCC or EHCC) is a storage-dependent compression format. It is supported on Exadata and selected Oracle storage platforms, including Oracle ZFS Storage Appliance, Pillar Axiom, FS1, SuperCluster, and Oracle Database Appliance (latest releases). It is not supported on ordinary third-party or non-Exadata storage.
If you have a Data Pump export from an Exadata database containing HCC-compressed tables, you can import it into a non-Exadata Oracle database. The important point is that the target table cannot remain HCC-compressed. Data Pump exports and imports the logical data, so it is the preferred method for creating a readable, uncompressed copy on non-Exadata storage.
When you import an HCC table into a non-Exadata database:
- Data Pump reads the exported data.
- Oracle creates the target table using storage attributes appropriate for the target database.
- The table is created uncompressed unless you explicitly choose another compression method supported by the target.
- The data becomes fully readable and queryable.
Oracle supports exporting and importing HCC tables with expdp and impdp.
Run this query on the Exadata source database:
SELECT owner,
table_name,
compression,
compress_for
FROM dba_tables
WHERE compression = ‘ENABLED’
ORDER BY owner, table_name;
Example output:
OWNER TABLE_NAME COMPRESSION COMPRESS_FOR
——- —————- ———– ————-
SCOTT SALES_HCC ENABLED QUERY LOW
SCOTT ORDERS_HCC ENABLED QUERY HIGH
SH SALES_HISTORY ENABLED ARCHIVE HIGH
Identify compressed partitions
HCC is often used for large partitioned tables. Check partition-level compression as well:
SELECT owner,
table_name,
partition_name,
compression,
compress_for
FROM dba_tab_partitions
WHERE compression = ‘ENABLED’
ORDER BY owner, table_name, partition_name;
SELECT owner,
segment_name,
segment_type,
ROUND(bytes/1024/1024) AS size_mb
FROM dba_segments
WHERE owner = ‘SCOTT’
AND segment_name = ‘SALES_HCC’;
Because HCC data is physically compressed, the decompressed size on the target can be much larger. Ensure the target database has enough space before starting the import.
Step-by-step Data Pump migration
Step 1: Create a Data Pump directory on the source
Connect to the Exadata source database:
CREATE OR REPLACE DIRECTORY DP_DIR AS ‘/u01/app/oracle/admin/ORCL/dpdump’;
GRANT READ, WRITE ON DIRECTORY DP_DIR TO scott;
Verify the directory:
SELECT directory_name, directory_path
FROM dba_directories
WHERE directory_name = ‘DP_DIR’;
Export selected HCC tables:
expdp system/*** \
directory=DP_DIR \
dumpfile=hcc_tables_%U.dmp \
logfile=hcc_tables_export.log \
tables=SCOTT.SALES_HCC,SCOTT.ORDERS_HCC \
parallel=4 \
compression=ALL
Export an entire schema:
expdp system/*** \
directory=DP_DIR \
dumpfile=scott_schema_%U.dmp \
logfile=scott_schema_export.log \
schemas=SCOTT \
parallel=4 \
compression=ALL
Useful Data Pump parameters:
| Parameter | Purpose |
| directory | Oracle directory object where dump files are written |
| dumpfile | Dump file name; %U generates multiple files when parallelism is used |
| logfile | Export log file |
| tables | Exports specific tables |
| schemas | Exports one or more schemas |
| parallel | Number of parallel worker processes |
| compression=ALL | Compresses metadata and data in the dump file |
Review the log for errors and confirm that the expected tables were exported:
grep -Ei “SALES_HCC|ORDERS_HCC|completed|error|ORA-” hcc_tables_export.log
A successful export normally ends with:
Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/ORCL/dpdump/hcc_tables_01.dmp
Job “SYSTEM”.”SYS_EXPORT_TABLE_01″ successfully completed
Step 4: Transfer the dump files
Copy the dump files to the non-Exadata target server:
scp /u01/app/oracle/admin/ORCL/dpdump/hcc_tables_*.dmp \
oracle@target-server:/u01/app/oracle/admin/TARGETDB/dpdump/
Alternatively, use rsync for resumable transfers:
rsync -avP \
/u01/app/oracle/admin/ORCL/dpdump/hcc_tables_*.dmp \
oracle@target-server:/u01/app/oracle/admin/TARGETDB/dpdump/
Step 5: Create a Data Pump directory on the target
Connect to the non-Exadata target database:
CREATE OR REPLACE DIRECTORY DP_DIR AS ‘/u01/app/oracle/admin/TARGETDB/dpdump’;
GRANT READ, WRITE ON DIRECTORY DP_DIR TO scott;
Confirm that the operating-system user can access the directory:
ls -l /u01/app/oracle/admin/TARGETDB/dpdump/
Step 6: Import without HCC storage attributes
Use TRANSFORM=SEGMENT_ATTRIBUTES:N so Data Pump does not carry over source storage attributes that may not be appropriate for the non-Exadata target.
Import selected tables:
impdp system/*** \
directory=DP_DIR \
dumpfile=hcc_tables_%U.dmp \
logfile=hcc_tables_import.log \
tables=SCOTT.SALES_HCC,SCOTT.ORDERS_HCC \
transform=SEGMENT_ATTRIBUTES:N \
transform=LOB_STORAGE:DEFAULT \
parallel=4
Import an entire schema:
impdp system/*** \
directory=DP_DIR \
dumpfile=scott_schema_%U.dmp \
logfile=scott_schema_import.log \
schemas=SCOTT \
transform=SEGMENT_ATTRIBUTES:N \
transform=LOB_STORAGE:DEFAULT \
parallel=4
Step 7: Remap schema or tablespace if required
If the target schema is different:
impdp system/*** \
directory=DP_DIR \
dumpfile=hcc_tables_%U.dmp \
logfile=hcc_tables_import.log \
tables=SCOTT.SALES_HCC \
remap_schema=SCOTT:SALES_APP \
transform=SEGMENT_ATTRIBUTES:N \
parallel=4
If the target tablespace is different:
impdp system/*** \
directory=DP_DIR \
dumpfile=hcc_tables_%U.dmp \
logfile=hcc_tables_import.log \
tables=SCOTT.SALES_HCC \
remap_tablespace=USERS:APP_DATA \
transform=SEGMENT_ATTRIBUTES:N \
parallel=4
Step 8: Verify the imported table
Check the compression setting:
SELECT owner,
table_name,
compression,
compress_for
FROM dba_tables
WHERE owner = ‘SCOTT’
AND table_name IN (‘SALES_HCC’,’ORDERS_HCC’);
Expected result on a non-Exadata target:
OWNER TABLE_NAME COMPRESSION COMPRESS_FOR
——- —————- ———– ————-
SCOTT SALES_HCC DISABLED
SCOTT ORDERS_HCC DISABLED
Validate row counts and sample data:
SELECT COUNT(*) FROM scott.sales_hcc;
SELECT *
FROM scott.sales_hcc
FETCH FIRST 10 ROWS ONLY;
Compare row counts between source and target:
— Source, Exadata
SELECT COUNT(*) AS source_rows FROM scott.sales_hcc;
— Target, non-Exadata
SELECT COUNT(*) AS target_rows FROM scott.sales_hcc;
For a stronger validation, compare checksums or use Data Pump’s NETWORK_LINK method for direct database-to-database transfer.
Alternative: direct network import
If the source and target databases can communicate over SQL*Net, you can avoid copying dump files by using NETWORK_LINK.
Create a database link on the target
CREATE DATABASE LINK exadata_src
CONNECT TO system IDENTIFIED BY “***”
USING ‘ORCL_PRIMARY’;
Import directly from the source
impdp system/*** \
directory=DP_DIR \
logfile=hcc_network_import.log \
network_link=exadata_src \
tables=SCOTT.SALES_HCC \
transform=SEGMENT_ATTRIBUTES:N \
parallel=4
This method is useful for smaller migrations or when network bandwidth between the source and target is reliable.
ORA-64307: Exadata Hybrid Columnar Compression is not supported for tablespaces on this storage type
This means Oracle tried to create or access an HCC segment on unsupported storage. For Data Pump imports, use:
transform=SEGMENT_ATTRIBUTES:N
Before import, estimate the required space:
SELECT owner,
table_name,
num_rows,
avg_row_len,
ROUND(num_rows * avg_row_len / 1024 / 1024) AS estimated_uncompressed_mb
FROM dba_tables
WHERE owner = ‘SCOTT’
AND table_name = ‘SALES_HCC’;
The estimate is approximate. Always leave additional free space for indexes, temporary segments, undo, and redo generation.
If the import fails with directory errors, verify:
SELECT directory_name, directory_path
FROM dba_directories
WHERE directory_name = ‘DP_DIR’;
Also confirm that the directory exists at the operating-system level and that the Oracle process owner has read and write permissions.
Use Data Pump when your objective is to migrate, copy, or archive HCC tables to a non-Exadata Oracle database.
- Use expdp on Exadata to export the tables.
- Use impdp with TRANSFORM=SEGMENT_ATTRIBUTES:N on the non-Exadata target.
- Validate compression settings, row counts, and sample data after import.
- Do not attempt to preserve HCC compression on unsupported storage.
Data Pump provides the cleanest and most predictable method for moving HCC data to a non-Exadata database.