Compress tables during import Oracle Database 12C release 1 (12.1)

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

2 thoughts on “Compress tables during import Oracle Database 12C release 1 (12.1)

  1. 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

Leave a Reply

Your email address will not be published. Required fields are marked *