Site icon IT Tutorial

ORA-12990: duplicate option specified

I got ” ORA-12990: duplicate option specified ” error in Oracle database during import ( impdp ).

 

ORA-12990: duplicate option specified

 

Details of error are as follows.

 

impdp \"/ as sysdba\" directory=DATA_PUMP dumpfile=MSDBA_PartOne%U.dmp schemas=MSDBA logfile=MSDBA_imp.log parallel=96 remap_schema=MSDBA:MEHMET

CREATE TABLE "MEHMET"."TEST_TABLE" ("OFFER_ID" VARCHAR2(30 CHAR) NOT NULL ENABLE, "OFFER_SDT" DATE NOT NULL ENABLE, "OFFER_EDT" DATE, "OFFER_NUM" VARCHAR2(30 CHAR), "VODAMONEY_NAME" VARCHAR2(100 CHAR), "VODAMONEY_DESC" VARCHAR2(200 CHAR), "INSERT_DT" DATE, "UPDATE_DT" DATE) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" READ ONLY PARTITION BY RANGE ("OFFER_EDT") SUBPARTITION BY HASH ("OFFER_ID") SUBPARTITIONS 16 (PARTITION "P_LOW" VALUES LESS THAN (TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" READ ONLY ( SUBPARTITION "SYS_SUBP3982692" SEGMENT CREATION DEFERRED STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" COLUMN STORE COMPRESS FOR QUERY HIGH NO ROW LEVEL LOCKING READ ONLY , SUBPARTITION "SYS_SUBP3982693" SEGMENT CREATION DEFERRED STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" COLUMN STORE COMPRESS FOR QUERY HIGH NO ROW LEVEL LOCKING READ ONLY , SUBPARTITION "SYS_SUBP3982694" SEGMENT CREATION DEFERRED STORAGE( BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "USERS" COLUMN STORE COMPRESS FOR QUERY HIGH NO ROW LEVEL LOCKING READ ONLY , SUBPARTITION "SYS_SUBP3982695" SE

ORA-39083: Object type TABLE:"MSDBA"."TEST_TABLE" failed to create with error:
ORA-12990: duplicate option specified


 

The ORA-12990 error is related with the export – import of Read Only Tables.

If you got this error, and if your tables are read only mode, then convert the tables into READ / WRITE as follows.

alter table <table_name> read write;

 

Once you convert the tables into read write mode, then export the tables again, then try to import again. You won’t get this error.

 

Second solution:

I have created the missing of tables manually, then I have started impdp with TABLE_EXISTS_ACTION=TRUNCATE, or You can create the tables manually, then you can use the insert into select command.

 

 

There is known Bug 31948408 – IMPDP FAILS WITH ORA-24067 DURING IMPORTING A BIG PARTITIONED TABLE WITH MULTIPL at status 11 which develoment is still investigating.

and the current known workaround is to :

import big partitioned tables with parallel=1 or MAX_DATAPUMP_PARALLEL_PER_JOB = 1 or 2.

 

 

Do you want to learn Oracle Partitioning, then Click This Link and read the articles.

Partitioning in Oracle Database

Exit mobile version