xmltype oracle imp,imp时遇到ora-907错误

1.

Looks like bug 4154125

BugTag: Support notes on Bug 4154125 - DDR info

BugDesc 4154125

Affects: RDBMS (-A2)

NB: FIXED

Abstract: Export writes bad SQL to DMP file for

XMLTYPE tables with DEFAULT clauses

Fixed-Releases: 9207 A105 A201

Tags: BADBACKUP CORR/EXP EXP XML

Details:

Incorrect SQL may be written to the export file for tables

with

schema based XML type columns. Subsequent import fails with

errors such as ORA-907. The problem is related to DEFAULT

clauses

existing in the table definition.

eg:

Export of a table with this definition writes incorrect SQL to the

.DMP file:

CREATE TABLE "DELFOR_TABLE" (

ID NUMBER PRIMARY KEY,

COMPANYID VARCHAR2(30) NOT NULL,

SNDREDI VARCHAR2(35) NOT NULL,

RECVEDI VARCHAR2(35) NOT NULL,

"DOC" "XMLTYPE",

TMPDOC CLOB DEFAULT NULL

)xmltype column doc

XMLSCHEMA "http://www.gxs.com/delfor.xsd"

element "Delfor";

To implement the solution, please execute the following

steps:

1. Upgrade to 9.2.0.7 or newer version to correct the known bug and

redo the export then retry the import.

2. Workaround as follows:

a.. alter table im_source_type modify (systime_def_id default

null); change the default

b.  alter table im_source_type modify (systime_def_id default

0); change the default back to original

setting

c. exp

d.  imp

2.

Applies to:

Oracle Server - Enterprise Edition - Version:

9.2.0.6.0

This problem can occur on any platform.

Symptoms

Import fails with ORA-00907: missing right

parenthesis for some tables and cannot continue.

It is happening consistently - the tables have comments defined on

their columns (be it inline comments or comments created later on

using the COMMENT ON command).

Cause

Bug 3944578 Abstract: IMP-3 ORA-907 DURING

IMPORTING A TABLE WITH COMMENTS

The bug is permanently fixed in 10.2

Solution

Use the bug workaround:

1. Import the data with show=y

2. Extract the problematic DDLs

3. Use them to create the tables

4. Import the data with IGNORE=Y