VMCD.ORG » 11.2.0.3 datapump got ORA-38500

从10.2.0.5的库导入一张表的增量数据到11.2.0.3 impdp报出如下错误,对比两边表结构一致，tablespace有剩余空间报错如下：

[oracle@localhost ~]$ impdp \’\/ as sysdba \’ directory=pump dumpfile=so.dmp TABLE_EXISTS_ACTION=append

Import: Release 11.2.0.3.0 – Production on Thu Feb 23 09:44:49 2012

reference:

Data Pump Import Fails With ORA-942 ORA-06512 And ORA-38500 [ID 1350414.1]

Master Note for Oracle XML Database (XDB) Install / Deinstall [ID 1292089.1]

11.2 – XDB Removal and Reinstall

XDB Removal

The catnoqm.sql script drops XDB.

SQL> spool xdb_removal.log
SQL> set echo on;
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup
SQL> @?/rdbms/admin/catnoqm.sql
SQL> spool off;

XDB Installation

The catqm.sql script requires the following parameters be passed to it when run:

A. XDB user password
B. XDB user default tablespace
(Any tablespace other than SYSTEM, UNDO and TEMP can be specified.
The specified tablespace must already exist prior to running the script.)
C. XDB user temporary tablespace
D. SecureFiles = YES or NO
(If YES is specified, the XDB repository will use SecureFile storage.
If NO is specified, LOBS will be used.
To use SecureFiles, compatibility must be set to 11.2.
The tablespace specified for the XDB repository must be using
Automatic Segment Space Management (ASSM) for SecureFiles to be used.)

Therefore the syntax to run catqm.sql is the following:
SQL> catqm.sql A B C D

For Example:
SQL> @?/rdbms/admin/catqm.sql XDB XDB TEMP YES

## IMPORTANT: You must shutdown and restart the database between removal and reinstall ##

SQL> spool xdb_install.log
SQL> set echo on;
SQL> connect / as sysdba
SQL> shutdown immediate;
SQL> startup;
SQL> @?/rdbms/admin/catqm.sql
SQL> @?/rdbms/admin/utlrp.sql
SQL> spool off

SQL> select owner, object_name, object_type, status
from dba_objects
where status = ‘INVALID’
and owner = ‘XDB’; 2 3 4

no rows selected

SQL> select comp_name, version, status
from dba_registry
where comp_id = ‘XDB’; 2 3

COMP_NAME
——————————————————————————–
VERSION STATUS
—————————— ———————————
Oracle XML Database
11.2.0.3.0 VALID

1 row selected.

SQL> !

重新导入：

[oracle@localhost ~]$ impdp \’\/ as sysdba \’ directory=pump dumpfile=so.dmp TABLE_EXISTS_ACTION=append

Import: Release 11.2.0.3.0 – Production on Thu Feb 23 09:44:49 2012

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table “SYS”.”SYS_IMPORT_FULL_01″ successfully loaded/unloaded
Starting “SYS”.”SYS_IMPORT_FULL_01″: “/******** AS SYSDBA” directory=pump dumpfile=so.dmp TABLE_EXISTS_ACTION=append
Processing object type TABLE_EXPORT/TABLE/TABLE
Table “USER_DATA2″.”SO” exists. Data will be appended to existing table but all dependent metadata will be skipped due to table_exists_action of append
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported “USER_DATA2″.”SO” 225.1 MB 428745 rows
Processing object type TABLE_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/COMMENT
Processing object type TABLE_EXPORT/TABLE/TRIGGER
Processing object type TABLE_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job “SYS”.”SYS_IMPORT_FULL_01″ successfully completed at 10:23:05