RE: Errors in impdp while copying xmltype

From: "Sweetser, Joe" <JSweetser@xxxxxxxx>
To: "nupendra@xxxxxxxxxxx" <nupendra@xxxxxxxxxxx>, Oracle-L <oracle-l@xxxxxxxxxxxxx>
Date: Thu, 25 May 2017 18:58:49 +0000

Hi,

Is "Oracle XML Database" installed? From my 12.1 database:

SYS@idm04> select comp_name ||' - '|| version ||' - '|| status from
dba_registry order by comp_name;

COMP_NAME||'-'||VERSION||'-'||STATUS
--------------------------------------------------------------------------------
JServer JAVA Virtual Machine - 12.1.0.2.0 - VALID
Oracle Database Catalog Views - 12.1.0.2.0 - VALID
Oracle Database Java Packages - 12.1.0.2.0 - VALID
Oracle Database Packages and Types - 12.1.0.2.0 - VALID
Oracle Multimedia - 12.1.0.2.0 - VALID
Oracle Text - 12.1.0.2.0 - VALID
Oracle Workspace Manager - 12.1.0.2.0 - VALID
Oracle XDK - 12.1.0.2.0 - VALID
Oracle XML Database - 12.1.0.2.0 - VALID

9 rows selected.

SYS@idm04>

Joe Sweetser
Sr. DBA/Nerd
ICAT
Underwriters at Lloyd's, Syndicate 4242

Security. Service. Recovery.

T: 303-327-1863
jsweetser@xxxxxxxx<mailto:jsweetser@xxxxxxxx> |
www.icat.com<http://www.icat.com/>

Follow Us
[cid:image001.jpg@01D2B1D2.C8D90500]<https://www.facebook.com/ICATinsurance/>[cid:image002.jpg@01D2B1D2.C8D90500]<https://www.linkedin.com/company/icat>
[cid:image003.jpg@01D2B1D2.C8D90500] <https://twitter.com/ICATinsurance>
[cid:image004.jpg@01D2B1D2.C8D90500] <https://www.instagram.com/icatinsurance/>
Thank you to our employees for naming ICAT a Top Workplace in Denver Metro for
the 3rd year in a row!

From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On ;
Behalf Of Upendra nerilla
Sent: Thursday, May 25, 2017 9:48 AM
To: Oracle-L <oracle-l@xxxxxxxxxxxxx>
Subject: Errors in impdp while copying xmltype

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
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 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT) TABLESPAC

Thanks much in advance

-Upendra
Confidentiality Note: This message contains information that may be
confidential and/or privileged. If you are not the intended recipient, you
should not use, copy, disclose, distribute or take any action based on this
message. If you have received this message in error, please advise the sender
immediately by reply email and delete this message. Although ICAT, Underwriters
at Lloyd's, Syndicate 4242, scans e-mail and attachments for viruses, it does
not guarantee that either are virus-free and accepts no liability for any
damage sustained as a result of viruses. Thank you.

References:
- Errors in impdp while copying xmltype
  - From: Upendra nerilla

RE: Errors in impdp while copying xmltype

Other related posts: