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