oracle impdp - maximum open cursors exceeded

Question

I have exported a database schema from an oracle 10.2.0.5.0 database with expdp, logged in as the owner of the schema.

Now I have tried to import the data on an Oracle 12.1.0.2.0 database impdp, logged in as sys with role sysdba.

The expdp command:

expdp "owner/password@hostname/servicename" schemas=SCHEMA_NAME directory=EXPDIR dumpfile=SCHEMA_NAME.dmp logfile=SCHEMA_NAME.log 

The impdp command:

impdp "sys/password@hostname/servicename as sysdba" schemas=SCHEMA_NAME directory=EXPDIR dumpfile=SCHEMA_NAME.dmp logfile=SCHEMA_NAME.log 

The import gave me the error message:

Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 1 with process name "DW00" prematurely terminated
ORA-31671: Worker process DW00 had an unhandled exception.
ORA-01000: maximum open cursors exceeded
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS [INDEX:"SCHEMA_NAME"."UK$SOME$NAME"] 
CREATE UNIQUE INDEX "SCHEMA_NAME"."UK$SOME$NAME" ON "SCHEMA_NAME"."TABLE_NAME" ("COLUMN_NAME") PCTFREE 10 INITRANS 2 MAXTRANS 255  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "TBLSPC" PARALLEL 1 
ORA-31625: Schema SYS is needed to import this object, but is unaccessible
ORA-00604: error occurred at recursive SQL level 2
ORA-01000: maximum open cursors exceeded
ORA-06512: at "SYS.KUPW$WORKER", line 9193
ORA-06512: at "SYS.KUPW$WORKER", line 22449
ORA-31625: Schema SYS is needed to import this object, but is unaccessible
ORA-00604: error occurred at recursive SQL level 2
ORA-01000: maximum open cursors exceeded
ORA-01000: maximum open cursors exceeded
ORA-39014: One or more workers have prematurely exited.
ORA-39029: worker 2 with process name "DW00" prematurely terminated
ORA-31671: Worker process DW00 had an unhandled exception.
ORA-01000: maximum open cursors exceeded
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PUT_DDLS [INDEX:"SCHEMA_NAME"."IDX$SOME$OTHER_NAME"] 
CREATE INDEX "SCHEMA_NAME"."IDX$SOME$OTHER_NAME" ON "SCHEMA_NAME"."OTHER_TABLE_NAME" ("OTHER_COLUMN_NAME") PCTFREE 10 INITRANS 2 MAXTRANS 255  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "TBLSPC" PARALLEL 1 
ORA-31625: Schema SYS is needed to import this object, but is unaccessible
ORA-00604: error occurred at recursive SQL level 2
ORA-01000: maximum open cursors exceeded
ORA-06512: at "SYS.KUPW$WORKER", line 9193
ORA-06512: at "SYS.KUPW$WORKER", line 22449
ORA-31625: Schema SYS is needed to import this object, but is unaccessible
ORA-00604: error occurred at recursive SQL level 2
ORA-01000: maximum open cursors exceeded
ORA-01000: maximum open cursors exceeded
Job "SYS"."SYS_IMPORT_SCHEMA_13" stopped due to fatal error at Fri Dec 1 14:09:07 2017 elapsed 0 00:02:05

I have tried to increase the maximum number of open cursors, but it did not help.

With the option EXCLUDE=INDEX, the import runs without errors, but I need them too.

What can I try next?

Answer 1

ORA-01000: Maximum Open Cursors Exceeded During DataPump Import (IMPDP) In 12c (Doc ID 2283800.1) Last updated on AUGUST 08, 2017

Invoking Data Pump Import

Do not invoke Import as SYSDBA, except at the request of Oracle technical support. SYSDBA is used internally and has specialized functions; its behavior is not the same as for general users.

Answer 2

ORA-01000: Maximum Open Cursors Exceeded During DataPump Job Execution In 12c (Doc ID 2283800.1)

Cause

The value of OPEN_CURSOR is not set high enough.

When doing a DataPump import, the number of cursors depends on various factors including number of objects, partitions, indexes, recursive SQLs involved, etc. Also, on 12c, the internal processing of impdp is more complex than 10g, so it may need a higher open_cursor in order to finish the import job.

Solution

Increase OPEN_CURSORS temporarily to a value high enough like 2000 and redo the DataPump job.