Thanks for all the feedback.
It turns out to be an issue where the application schema was referring to "SYS"
xmltype column. And we were migrating only the application schemas which didn't
have all the information.
After we pointed out development were able to decouple SYS dependency which
fixed the migration issue.
________________________________
From: Andy Wattenhofer <watt0012@xxxxxxx>
Sent: Thursday, May 25, 2017 3:35 PM
To: nupendra@xxxxxxxxxxx
Cc: Oracle-L
Subject: Re: Errors in impdp while copying xmltype
That is a known bug. Please consult support doc
1455460.1<https://support.oracle.com/epmos/faces/DocContentDisplay?_afrLoop=485426811789962&id=1455460.1&_afrWindowMode=0&_adf.ctrl-state=15ld0l81ha_140>
for the workaround.
On Thu, May 25, 2017 at 10:47 AM, Upendra nerilla
<nupendra@xxxxxxxxxxx<mailto:nupendra@xxxxxxxxxxx>> wrote:
Hello everyone -
I am trying to migrate a schema from oracle 11.2 to 12.2. I do have a few
tables that have XMLTYPE columns.
There are no errors during export and while importing all the tables with
XMLTYPE columns are failing..
Any help or pointers are appreciated.
Here is an example error:
ORA-39083: Object type TABLE:"AEC_173DA6E"."AEI_TR_5086348" failed to create
with error:
ORA-00904: : invalid identifier
Failing sql is:
CREATE TABLE "AEC_173DA6E"."AEI_TR_5086348" ("ID" NUMBER NOT NULL ENABLE,
"RM_5086348_ID" NUMBER NOT NULL ENABLE, "CLIENT_ID" NUMBER NOT NULL ENABLE,
"TYPE" NUMBER NOT NULL ENABLE, "EFFECTIVE_F
ROM" TIMESTAMP (6) NOT NULL ENABLE, "EFFECTIVE_TO" TIMESTAMP (6) NOT NULL
ENABLE, "SYSTEM_DATE_FROM" TIMESTAMP (6) NOT NULL ENABLE, "SYSTEM_DATE_TO"
TIMESTAMP (6) NOT NULL ENABLE, "UPDATED_BY_U
SER_ID" NUMBER, "UPDATED_BY_ORIGINAL_USER_ID" NUMBER, "UPDATED_BY_SESSION_ID"
NUMBER, "UPDATED_BY_LOAD_ID" NUMBER, "UPDATED_BY_WORKFLOW_ID" NUMBER,
"UPDATED_BY_WORKFLOW_VERSION_ID" NUMBER, "UPD
ATED_BY_BR_ID" NUMBER, "F_84480045" "XMLTYPE", "F_84489428" "XMLTYPE",
"F_84440002" "XMLTYPE", "F_84435984" "XMLTYPE", "F_84509629" "XMLTYPE",
"F_84502918" "XMLTYPE", "F_84435788" "XMLTYPE", "F
_84469108" "XMLTYPE", "F_84477909" "XMLTYPE", "F_84528590" "XMLTYPE",
"F_84533802" "SYS"."XMLTYPE" ) SEGMENT CREATION IMMEDIATE PCTFREE 10 PCTUSED 40
INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS
2147483645<tel:(214)%20748-3645> PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPACE "A
LPHA_DATA" VARRAY "SYS_NC00078$"."values"."value" STORE AS TABLE
"SYS_NTPIMsnis0FyjgU6QKggpHeA=="(PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
LOGGING STORAGE(INITIAL 65536 NEXT 1048576 MINEX
TENTS 1 MAXEXTENTS 2147483645<tel:(214)%20748-3645> PCTINCREASE 0 FREELISTS 1
FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE
DEFAULT) TABLESPAC
Thanks much in advance
-Upendra
