In Oracle 12C it’s possible to specify during import compressions settings for a table independent from export settings.
It gives more flexibility during importing data.
impdp .. TRANSFORM=TABLE_COMPRESSION_CLAUSE:NONE|"<compression type>"
- NONE – compression ignored during import, taken from tablespace settings
- <compression type> – valid compression clause:
- NOCOMPRESS : Disables table compression.
- COMPRESS : Enables basic table compression.
- ROW STORE COMPRESS: Same as COMPRESS.
- ROW STORE COMPRESS BASIC : Same as COMPRESS.
- ROW STORE COMPRESS ADVANCED : Enables advanced compression, also known as OLTP compression.
- COLUMN STORE COMPRESS FOR {QUERY|ARCHIVE} : Hybrid Columnar Compression (HCC) available in Exadata and ZFS storage appliances.
- Can be set in PL/SQL using DBMS_DATAPUMP.METADATA_TRANSFORM
Example
Import data with dedicated compression type
impdp hr/hr DIRECTORY=dpdump_dir DUMPFILE=hr.dmp TRANSFORM=TABLE_COMPRESSION_CLAUSE:"ROW STORE COMPRESS"
Have a fun 🙂
Tomasz
TRANSFORM=TABLE_COMPRESSION_CLAUSE:”COLUMN STORE COMPRESS FOR QUERY”
ORA-31600: invalid input value COLUMN for parameter TABLE_COMPRESSION_CLAUSE in function DBMS_DATAPUMP.METADATA_TRANSFORM
It should work according to Oracle help
Regards
Tomasz