IMPDP + ORA-64307: hybrid columnar compression is not supported for tablespaces on this storage type

Today there was request from application team to import one of the schema from dev environment to uat one. I took schema level expdp backup & copied dumpfile to target environment.

When I started import using datapump I got ORA-64307 😦


ORA-39083: Object type TABLE:"FTC"."TDS_TRD" failed to create with error:
ORA-64307: hybrid columnar compression is not supported for tablespaces on this storage type
Failing sql is:
CREATE TABLE "FTC"."TDS_TRD" ("ID" NUMBER(19,0) NOT NULL ENABLE, "CREATED" TIMESTAMP (6) NOT NULL ENABLE, "DATA_TRANSFER_TYPE" VARCHAR2(20 BYTE) NOT NULL ENABLE, "EVENT_DATE" DATE NOT NULL ENABLE, "EVENT_SUBTYPE" VARCHAR2(40 BYTE), "EVENT_TYPE" VARCHAR2(40 BYTE) NOT NULL ENABLE, "FEED_SYSTEM_ID" VARCHAR2(10 BYTE) NOT

I found import of many tables failed giving same error.

I tried to get DDL for same table from source :


CREATE TABLE FTC.TDS_TRD
(
 ID NUMBER(19) NOT NULL,
 CREATED TIMESTAMP(6) NOT NULL,
 DATA_TRANSFER_TYPE VARCHAR2(20 BYTE) NOT NULL,
 EVENT_DATE DATE NOT NULL,
 EVENT_SUBTYPE VARCHAR2(40 BYTE),
 EVENT_TYPE VARCHAR2(40 BYTE) NOT NULL,
 FEED_SYSTEM_ID VARCHAR2(10 BYTE) NOT NULL,
 INTERMEDIARY_SYSTEM_ID VARCHAR2(10 BYTE),
 SOURCE_SYSTEM_ID VARCHAR2(10 BYTE) NOT NULL
)
TABLESPACE FTC_DATA
PCTUSED 0
INITRANS 1
MAXTRANS 255
STORAGE (
 PCTINCREASE 0
 BUFFER_POOL DEFAULT
 )
NOLOGGING 
COMPRESS FOR ARCHIVE LOW 
NOCACHE
NOPARALLEL
MONITORING;

After searching on metalink I found: Advanced Compression Licensing (Doc ID 1461614.1) ,which says:

Hybrid Columnar Compression is a feature of the Enterprise Edition of Oracle Database 11g that is dependent on the underlying storage system. Only Exadata storage, ZFS storage, and Pillar Axiom 600 storage support Hybrid Columnar Compression. On storage platforms other than those previously mentioned, Hybrid Columnar Compression is not available, and no right to use Hybrid Columnar Compression is granted by a license of the Oracle Database, and any attempt to use the feature may result in ORA-64307.

Of course as error suggested we were using EXADATA storage on source while dev environment was on non exadata one.


So as resolve the issue, I used one of the very useful parameters in impdp – TRANSFORM : – which enables you to alter object creation DDL for specific objects.

Using SEGMENT_ATTRIBUTES:n along with TRANSFORM, we can exclude segment attributes (physical attributes, storage attributes, tablespaces, and logging).

My Impdp statement was this :

impdp directory=EXPDP dumpfile=FTC_07oct14.dmp logfile=FTC_07oct14_imp.log schemas=FTC transform=segment_attributes:n:table


Hope so u will find this post very useful 🙂

Cheers

Regards,

Adityanath

Advertisements

7 thoughts on “IMPDP + ORA-64307: hybrid columnar compression is not supported for tablespaces on this storage type

  1. Hi Aditya,
    i have also faced the same error. but in my requirement is omit the storage clauses and need to import the tablespace info as same as import file. can you please guide me on this.

    • Hello Raavinaveen,

      Thanks for visiting the blog!!

      Yes its possible.

      The SEGMENT_ATTRIBUTES transform applies to both storage and tablespace attributes. To omit only the STORAGE clause and retain the TABLESPACE clause, you can use the STORAGE transform, as follows:

      TRANSFORM=STORAGE:n:table

      Addition to this you can also use the impdp transform=segment_attributes:n with other arguments such as remap_tablespace to change the tablespace names for all table in the import dump file.

      Regards,
      Adi

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s