exp/imp出現錯誤通過expdp/impdp來解決

ERP系統版本更新原因需要在測試系統上通過IMP/EXP方式進行了備份恢復資料，在imp方式匯入時發現如下錯誤提示：

Column 17

Column 18

Column 19

Column 20 CanEmpty{}|CheckLenX{FEditLen}|CheckLenMax{30}

Column 21 2

Column 22 com.kingdee.eas.base.form.ide.model.property.DefVa...

Column 23

IMP-00019: row rejected due to ORACLE error 1

IMP-00003: ORACLE error 1 encountered

ORA-00001: unique constraint (GDERPTEST.PK_T_BAS_ELEPROP) violated

Column 1 C8t8LfYGBE2XpayN4Ztr160AB873

Column 2 22

Column 3 Default Val

Column 4 ???

Column 5 ???

Column 6 Default Val

[@more@]

另外一個異常就是，在匯入時出現將資料匯入到原來用的表空中（不是新建使用者預設的表空間），因此按expdp/impdp方式進行備份恢復的測試。具體過程如下:

1.建立轉儲檔案(存放匯出資料的檔案)存放的目錄

[oracle@gderp1 data]$ sqlplus / as sysdba

SQL> create directory dump_dir as '/data/dump_dir'

SQL> grant read,write on directory dump_dir to gderp;

expdp gderp/gderp dumpfile=dump_dir:gderp0711.dmp logfile=dump_dir:gderp0711.log

匯出完畢後在執行如下命令進行了備份的匯入：

impdp gderpnew/gderpnew schemas="gderp" remap_schema="gderp:gderpnew" dumpfile=dump_dir:gderp0711.dmp logfile=dump_dir:gderpnew_imp.log

通過如下語句執行後，還是發現將資料匯入到原來表空間而不是新建使用者預設表空間。

expdp gderp/gderp dumpfile=dump_dir:gderp0711.dmp logfile=dump_dir:gderp0711.log

因此採用如下方法，檢視了匯出DMP檔案中的有關DDL語句：

impdp system/system schemas="gderp" dumpfile=dump_dir:gderp0711.dmp nologfile=y sqlfile=dump_dir:gderp_full.sql

執行過程如下：

[oracle@gderp1 data]$ impdp system/system schemas="gderp" dumpfile=dump_dir:gderp0711.dmp nologfile=y sqlfile=dump_dir:gderp_full.sql

Import: Release 10.2.0.1.0 - 64bit Production on Monday, 11 July, 2011 13:06:27

Copyright (c) 2003, 2005, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Data Mining options

Master table "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully loaded/unloaded

Starting "SYSTEM"."SYS_SQL_FILE_SCHEMA_01": system/******** schemas=gderp dumpfile=dump_dir:gderp0711.dmp nologfile=y sqlfile=dump_dir:gderp_full.sql

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION

Processing object type SCHEMA_EXPORT/PROCEDURE/PROCEDURE

Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION

Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE

Processing object type SCHEMA_EXPORT/VIEW/VIEW

Processing object type SCHEMA_EXPORT/TABLE/INDEX/FUNCTIONAL_AND_BITMAP/INDEX

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/FUNCTIONAL_AND_BITMAP/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYSTEM"."SYS_SQL_FILE_SCHEMA_01" successfully completed at 13:08:48

執行完畢後，檢視gderp_full.sql檔案內容，部分內容如下所示：

[oracle@gderp1 dump_dir]$ more gderp_full.sql

-- CONNECT SYSTEM

-- new object type path is: SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

-- CONNECT GDERP

BEGIN

sys.dbms_logrep_imp.instantiate_schema(schema_name=>SYS_CONTEXT('USERENV','CURRENT_SCHEMA'), export_db_name=>'EAS', inst_scn=>'108184772');

COMMIT;

END;

/

-- new object type path is: SCHEMA_EXPORT/TABLE/TABLE

-- CONNECT SYSTEM

CREATE TABLE "GDERP"."DW_BD_PUMPRECORD"

( "FTABLE" VARCHAR2(80),

"FSYSTEM" NUMBER(10,0),

"FCURRENTPERIODID" VARCHAR2(44),

"FBEGINDATE" TIMESTAMP (6),

"FENDDATE" TIMESTAMP (6),

"FISCLOSEDACCOUNT" NUMBER(10,0),

"FPUMPTIME" TIMESTAMP (6)

) 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)

TABLESPACE "EASDATA" ;

…

原來是將建立表和索引等物件的TABLESPACE "EASDATA" 屬性已經寫死的，知道原因，則解決起來就簡單了。

解決辦法如下，首先用ultraedit開啟gderp_full.sql並將TABLESPACE "EASDATA" 屬性批量替換為想要的表空間，然後在SQL Plus裡呼叫該sql檔案並建立這些物件的定義，最後根據content=DATA_ONLY選項只匯入資料；

impdp gderpnew/gderpnew schemas="gderp" remap_schema="gderp:gderpnew" content=DATA_ONLY dumpfile=dump_dir:gderp0711.dmp logfile=dump_dir:gderpnew_imp.log

注：

content=content_option Specifies whether data, metadata, or both are imported.

Valid values are: DATA_ONLY (data only), METADATA_ONLY

(metadata only), and the default ALL (both).

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/18841027/viewspace-1052527/，如需轉載，請註明出處，否則將追究法律責任。