客户需求,把测试库(12.2环境)的用户迁移到生成环境(19.18)中,expdp备份正常,impdp报错如下:
ORA-39083: Object type TABLE:"USR_JW"."T_XJGL_ZJB_XX" failed to create with error:
ORA-00959: tablespace 'TS_JW' does not exist
Failing sql is:
CREATE TABLE "USR_JW"."T_XJGL_ZJB_XX" ("WID" VARCHAR2(64 BYTE) NOT NULL ENABLE, "XH" VARCHAR2(32 BYTE) NOT NULL ENABLE, "XWZH" VARCHAR2(64 BYTE), "SYRQ" DATE, "BZ" VARCHAR2(5
12 BYTE), "ZSBPZTDM" VARCHAR2(8 BYTE), "BPSJ" DATE, "JYZTDM" VARCHAR2(8 BYTE), "JYSJ" DATE, "JYQK" VARCHAR2(2048 BYTE), "SFDY" VARCHAR2(8 BYTE), "DYSJ" DATE, "DYR" VARCHAR2(64 BYTE), "
DYRXM" VARCHAR2(64 BYTE), "CZR" VARCHAR2(64 BYTE), "CZRXM" VARCHAR2(64 BYTE), "CZSJ" DATE, "XWPCDM" VARCHAR2(64 BYTE), "BY1" CLOB, "BY2" CLOB, "BY3" CLOB, "BY4" CLOB, "BY5" CLOB, "BY6"
CLOB, "BY7" CLOB, "BY8" CLOB, "BY9" CLOB, "BY10" CLOB, "XZXM" VARCHAR2(64 BYTE), "SFAYJXKSY" NUMBER(10,0), "SFFB" VARCHAR2(10 BYTE), "SYRQYW" VARCHAR2(180 BYTE), "CXXWFJ" VARCHAR2(64
BYTE), "CXXWYY" VARCHAR2(2000 BYTE), "SFDYYWXWZ" VARCHAR2(10 BYTE), "CXXWRQ" DATE) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING 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 "
TS_JW" LOB ("BY1") STORE AS SECUREFILE ( TABLESPACE "TS_JW" ENABLE STORAGE IN ROW CHUNK 8192 NOCACHE LOGGING NOCOMPRESS KEEP_DUPLICATES STORAGE(INITIAL 106496 NEXT 104857
6 MINEXTENTS 1 MAXEXTENTS 2147483645 PCTINCREASE 0 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)) LOB ("BY2") STORE AS SECUREFILE ( TABLESPACE "TS_JW" ENABLE
STORAGE IN ROW CHUNK 8192 NO
ORA-39083: Object type TABLE:"USR_JW"."T_XJGL_ZSGL_XX" failed to create with error:
ORA-00959: tablespace 'TS_JW' does not exist
....................
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
ORA-39112: Dependent object type COMMENT skipped, base object type TABLE:"USR_JW"."T_XJGL_ZJB_XX" creation failed
ORA-39112: Dependent object type COMMENT:"ZDDM" skipped, base object type TABLE:"USR_JW"."T_XJGL_ZJB_XX" creation failed
ORA-39112: Dependent object type COMMENT:"ZDMC" skipped, base object type TABLE:"USR_JW"."T_XJGL_ZJB_XX" creation failed
ORA-39112: Dependent object type COMMENT:"FZMC" skipped, base object type TABLE:"USR_JW"."T_XJGL_ZJB_XX" creation failed
ORA-39112: Dependent object type COMMENT:"SFSY" skipped, base object type TABLE:"USR_JW"."T_XJGL_ZJB_XX" creation failed
ORA-39112: Dependent object type COMMENT:"PX" skipped, base object type TABLE:"USR_JW"."T_XJGL_ZJB_XX" creation failed
ORA-39112: Dependent object type COMMENT:"CZZ" skipped, base object type TABLE:"USR_JW"."T_XJGL_ZJB_XX" creation failed
ORA-39112: Dependent object type COMMENT:"CZZXM" skipped, base object type TABLE:"USR_JW"."T_XJGL_ZJB_XX" creation failed
ORA-39112: Dependent object type COMMENT:"CZRQ" skipped, base object type TABLE:"USR_JW"."T_XJGL_ZJB_XX" creation failed
ORA-39112: Dependent object type COMMENT:"BY1" skipped, base object type TABLE:"USR_JW"."T_XJGL_ZJB_XX" creation failed
ORA-39112: Dependent object type COMMENT:"BY2" skipped, base object type TABLE:"USR_JW"."T_XJGL_ZJB_XX" creation failed
ORA-39112: Dependent object type COMMENT:"BY3" skipped, base object type TABLE:"USR_JW"."T_XJGL_ZJB_XX" creation failed
ORA-39112: Dependent object type COMMENT:"BY4" skipped, base object type TABLE:"USR_JW"."T_XJGL_ZJB_XX" creation failed
ORA-39112: Dependent object type COMMENT:"BY5" skipped, base object type TABLE:"USR_JW"."T_XJGL_ZJB_XX" creation failed
ORA-39112: Dependent object type COMMENT:"BY6" skipped, base object type TABLE:"USR_JW"."T_XJGL_ZJB_XX" creation failed
ORA-39112: Dependent object type COMMENT:"BY7" skipped, base object type TABLE:"USR_JW"."T_XJGL_ZJB_XX" creation failed
ORA-39112: Dependent object type COMMENT:"BY8" skipped, base object type TABLE:"USR_JW"."T_XJGL_ZJB_XX" creation failed
ORA-39112: Dependent object type COMMENT:"BY9" skipped, base object type TABLE:"USR_JW"."T_XJGL_ZJB_XX" creation failed
ORA-39112: Dependent object type COMMENT:"BY10" skipped, base object type TABLE:"USR_JW"."T_XJGL_ZJB_XX" creation failed
ORA-39112: Dependent object type COMMENT:"BY11" skipped, base object type TABLE:"USR_JW"."T_XJGL_ZJB_XX" creation failed
ORA-39112: Dependent object type COMMENT:"BY12" skipped, base object type TABLE:"USR_JW"."T_XJGL_ZJB_XX" creation failed
ORA-39112: Dependent object type COMMENT:"BY13" skipped, base object type TABLE:"USR_JW"."T_XJGL_ZJB_XX" creation failed
ORA-39112: Dependent object type COMMENT:"BY14" skipped, base object type TABLE:"USR_JW"."T_XJGL_ZJB_XX" creation failed
ORA-39112: Dependent object type COMMENT:"BY15" skipped, base object type TABLE:"USR_JW"."T_XJGL_ZJB_XX" creation failed
ORA-39112: Dependent object type COMMENT:"SSBQ" skipped, base object type TABLE:"USR_JW"."T_XJGL_ZJB_XX" creation failed
处理办法:
缺少表空间TS_JW这个好处理,直接新增表空间即可,ORA-39112第一次遇到。清理环境后,新加TS_JW表空间后,再次impdp测试,发现ORA-39112: Dependent object type COMMENT报错也没有了。
该环境中ORA-39112的故障是由ORA-00959导致的问题