No question at this time
DBA Top 10
1 B. Vroman 14600
2 M. Cadot 11000
3 J. Schnackenberg 8200
4 T. Boles 7950
5 A. Kavsek 6200
6 M. Hidayathullah ... 2200
7 G. Lambregts 1100
8 T. P 1000
9 P. Wisse 900
10 B. Derous 500
10 . Lauri 500
10 R. Pattyn 500
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48286
Total active users1591
Act. users last 24h3
Act. users last hour0
Registered user hits last week242
Registered user hits last month1121
Go up

Error on datapump import on XMLSCHEMA objects
Next thread: Not able to down the CRS services for Os patching
Prev thread: ROLES AND ASSOCIATED PRIVILEGES !

Message Score Author Date
Hello, I am attempting to run a full datapump i...... Cedric Carbier Sep 12, 2018, 15:21
Cedric, Did you ever find the answer to this qu...... Tim Boles Sep 14, 2018, 14:39
Hi Tim, In the mean time, I have found that the...... Cedric Carbier Sep 14, 2018, 14:58
Woups, I have forgotten to answer your question......... Cedric Carbier Sep 14, 2018, 15:01
Oh....then that is different. That is a bug.. ...... Tim Boles Sep 14, 2018, 15:32
Great ! Thank you very much Tim :-) The worka...... Cedric Carbier Sep 14, 2018, 16:44

Follow up by mail Click here


Subject: Error on datapump import on XMLSCHEMA objects
Author: Cedric Carbier, France
Date: Sep 12, 2018, 15:21, 67 days ago
Os info: AIX 6.1
Oracle info: 11.2.0.4
Error info: ORA-39083
ORA-31086
Message: Hello,

I am attempting to run a full datapump import on a 11.2.0.4 database.
The source and the target databases include the XDB component, and the dump involved contains XMLSCHEMA objects.

The import command line is as simple as possible :

impdp "'/ as sysdba'" directory=DATA_PUMP_EXP FULL=Y dumpfile=exp_20180831.dmp logfile=imp_20180831.log table_exists_action=replace


Still the import fails with a set of messages like these ones :


ORA-39083: Echec de la cr▒ation du type d'objet XMLSCHEMA avec erreur :
ORA-31086: privil▒ges insuffisants pour l'inscription du sch▒ma "CompteRendu.xsd"
SQL en ▒chec :
BEGIN dbms_xmlschema.registerSchema(:1, :2, (:3 = 1), FALSE,FALSE,FALSE,FALSE, :4, options=> :5, schemaoid => :6, import_options => :7); END;


The question is : what are the missing privileges ? And for which user(s) ?

Thank you very much for your help.

Best regards,
Cedric

Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Error on datapump import on XMLSCHEMA objects
Author: Tim Boles, United States
Date: Sep 14, 2018, 14:39, 65 days ago
Message: Cedric,

Did you ever find the answer to this question? I am extremely curious as to what is going on now with this. If you are importing as the sys user the only thing that I can even think (unless this is a bug of some type) is the use of VPDs is involved some how some way. That might be a totally wrong thought but it is one area I would consider looking.

Did you open an SR with Oracle yet?

Tim
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Error on datapump import on XMLSCHEMA objects
Author: Cedric Carbier, France
Date: Sep 14, 2018, 14:58, 65 days ago
Message: Hi Tim,

In the mean time, I have found that the errors fire on some table creations including XML Schemas.
(I assume I know few things about XML Schema's functionalities)

For instance, the DDL of one of these tables looks like this :


CREATE TABLE "OWNER"."WCRP"
( "EHR_C_EH" CHAR(5 CHAR) NOT NULL ENABLE,
"CRP_C_TYPE" CHAR(3 CHAR) NOT NULL ENABLE,
"CRP_D_TRAITEMENT" DATE NOT NULL ENABLE,
"CRP_C_ETAT" CHAR(1 CHAR) NOT NULL ENABLE,
"CRP_X_DOC" "SYS"."XMLTYPE" NOT NULL ENABLE,
"CRP_C_IDMETIER" VARCHAR2(15 CHAR),
"CRP_C_IDTAUX" VARCHAR2(18 CHAR)
) 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 "XMLDATA"
XMLTYPE COLUMN "CRP_X_DOC" STORE AS BASICFILE CLOB (
TABLESPACE "XMLDATA" ENABLE STORAGE IN ROW CHUNK 8192
NOCACHE 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))
XMLSCHEMA "CompteRendu.xsd" ELEMENT "rapport" ID 19245 ;


... which throws the message below :

ORA-31000: La ressource 'CompteRendu.xsd' n'est pas un document de sch▒ma XDB


Basically, this is true. When running this query...

select schema_url from all_xml_schemas

... it's clear that "CompteRendu.xsd" is not registered.

But I wonder how to register this file in the database's repository (I do not understand how the dbms_xmlschema.registerschema procedure works).

Best regards,
Cedric
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Error on datapump import on XMLSCHEMA objects
Author: Cedric Carbier, France
Date: Sep 14, 2018, 15:01, 65 days ago
Message: Woups, I have forgotten to answer your question...

I have opened a SR at Metalink. No answer yet.

Best regards,
Cedric
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Error on datapump import on XMLSCHEMA objects
Author: Tim Boles, United States
Date: Sep 14, 2018, 15:32, 65 days ago
Message: Oh....then that is different.

That is a bug..

DataPump Import (Impdp) Fails With ORA-39083 ORA-31000: Resource <voc_cardiac.xsd> Is Not An XDB Schema Document (Doc ID 1937363.1)

BUG:18458648 - IMPORT OF TABLE WITH XMLTYPE FAILS WITH ORA-31000
BUG:18672264 - ORA-39083, ORA-31000 FROM IMPDP FOR XMLSCHEMA
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Error on datapump import on XMLSCHEMA objects
Author: Cedric Carbier, France
Date: Sep 14, 2018, 16:44, 65 days ago
Message: Great !
Thank you very much Tim :-)

The workaround given consists in pre-registering the xsd files.
This is what I did... sometimes it works, and sometimes not.

For instance :


declare
l_xml XMLTYPE;
BEGIN
dbms_xmlschema.registerschema
( schemaurl => 'F4DS_2014.xsd'
, schemadoc => BFILENAME('N4DS_DIR','F4DS_2014.xsd')
, LOCAL => TRUE
, gentypes => TRUE
, genbean => FALSE
, gentables => TRUE
);

dbms_output.put_line('F4DS_2014.xsd registered');
END;
/


Gives :

ORA-31084: erreur lors de la création de la table "OWNER"."XML_R_X4DS3421_TAB" pour l'élément "XML_R_X4DS"
ORA-02320: échec de création d'une table de stockage pour la colonne de table imbriquée "XMLDATA"."S30.G01.00"."S40.G01.00"
ORA-01792: le nombre maximum de colonnes d'une table ou d'une vue est 1000
ORA-02310: le nombre maximum de colonnes autorisées dans une table est dépassé
ORA-06512: à "XDB.DBMS_XMLSCHEMA_INT", ligne 37
ORA-06512: à "XDB.DBMS_XMLSCHEMA", ligne 65
ORA-06512: à "XDB.DBMS_XMLSCHEMA", ligne 136
ORA-06512: à ligne 4


I am now looking for a solution for this.

Best regards,
Cedric
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here