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