oracle impdp

oracle impdp - 超出最大打开游标数(oracle impdp - maximum open cursors exceeded)

我已经使用expdp从oracle 10.2.0.5.0数据库中导出了一个数据库模式，并以该模式的所有者身份登录。

现在我已经尝试将数据导入到oracle 12.1.0.2.0数据库impdp中，并以角色sysdba以sys身份登录。

expdp命令：

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

impdp命令：

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

导入给了我错误消息：

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

我试图增加打开游标的最大数量，但它没有帮助。

使用选项EXCLUDE = INDEX导入运行没有错误，但我也需要它们。

我接下来可以尝试什么？

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?

最满意答案

ORA-01000：在12c中DataPump导入（IMPDP）期间超过最大打开游标数（Doc ID 2283800.1）最后更新日期：2017年8月8日

调用数据泵导入

除非应Oracle技术支持部门的要求，否则不要以SYSDBA的形式调用Import。 SYSDBA在内部使用并具有专门的功能; 它的行为与普通用户不一样。

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.