在上篇《IMP同庫Type物件匯入報錯ORA-02304》（http://space.itpub.net/17203031/viewspace-732089）中，我們討論了由於type使用特性的原因，如果我們使用exp/imp工具匯入到相同資料庫中，是會發生報錯現象。

當我們使用exp/imp的時候，報錯ORA-02304實際上是沒有什麼特別好的解決方法的。Type匯入相同庫報錯的本質在於在匯出的時候，Oracle會將type的oid連帶匯出。而匯入的時候，又希望將其還原為相同的oid從而引發衝突。

那麼，是不是我們就沒有辦法了呢？我們藉助Oracle 10g提出的資料泵（Data Dump）工具，是可以避免這個問題的。

1、環境準備

我們同樣適用Oracle 11gR2進行試驗。

SQL> select * from v$version;

BANNER

--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

在scott使用者下，我們建立一些type型別物件。

SQL> grant imp_full_database to scott;

Grant succeeded

SQL> grant exp_full_database to scott;

Grant succeeded

SQL> conn scott/tiger@wilson;

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scott

SQL> create type mt_type as object (xm number, tchar varchar2(10));

2 /

Type created

SQL> select type_name, type_oid from user_types;

TYPE_NAME TYPE_OID

------------------------------ --------------------------------

MT_TYPE C230A55B1FC34E1DE040A8C0580017C6

SQL> create table my_tabletype of mt_type;

Table created

SQL> insert into my_tabletype values (1,'df');

1 row inserted

SQL> commit;

Commit complete

之後，我們建立使用者scottback。使用資料泵expdp從scott中將資料匯出。

SQL> create user scottback identified by scottback;

User created

SQL> grant resource to scottback;

Grant succeeded

SQL> grant connect to scottback;

Grant succeeded

SQL> grant exp_full_database to scottback;

Grant succeeded

SQL> grant imp_full_database to scottback;

Grant succeeded

2、expdp資料匯出

資料泵DataDump作為10g中推出的新一代資料備份還原工具，具有很多好的特點。DataDump是伺服器端使用工具，需要在伺服器上執行。

首先，我們需要建立directory物件，對應伺服器上的一個目錄位置。

[root@oracle11g /]# pwd

/

[root@oracle11g /]# mkdir export

[root@oracle11g /]# ls -l | grep export

drwxr-xr-x 2 root root 4096 Jun 11 19:29 export

[root@oracle11g /]# chown oracle:oinstall export

[root@oracle11g /]# ls -l | grep export

drwxr-xr-x 2 oracle oinstall 4096 Jun 11 19:39 export

建立directory物件，並且將read write許可權授予給scott和scottback。

SQL> create or replace directory MY_DIR

2 as '/export';

Directory created

SQL> grant write, read on directory my_dir to scott;

Grant succeeded

SQL> grant write, read on directory my_dir to scottback;

Grant succeeded’

再使用expdp命令列進行匯出。

[oracle@oracle11g ~]$ cd /export/

[oracle@oracle11g export]$ pwd

/export

[oracle@oracle11g export]$ expdp scott/tiger@wilson directory=my_dir dumpfile=scott.dmp logfile=resexp.log schemas=scott

Export: Release 11.2.0.1.0 - Production on Mon Jun 11 19:35:08 2012

[oracle@oracle11g export]$ expdp scott/tiger@wilson directory=my_dir dumpfile=scott.dmp logfile=resexp.log schemas=scott

Export: Release 11.2.0.1.0 - Production on Mon Jun 11 19:35:08 2012

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT"."SYS_EXPORT_SCHEMA_01": scott/********@wilson directory=my_dir dumpfile=scott.dmp logfile=resexp.log schemas=scott

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

（篇幅原因，部分省略…..）

. . exported "SCOTT"."T" 0 KB 0 rows

. . exported "SCOTT"."T1" 0 KB 0 rows

. . exported "SCOTT"."T2" 0 KB 0 rows

Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:

/export/scott.dmp

Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 19:36:00

[oracle@oracle11g export]$ ls -l

total 420

-rw-r--r-- 1 oracle oinstall 2467 Jun 11 19:36 resexp.log

-rw-r----- 1 oracle oinstall 421888 Jun 11 19:36 scott.dmp

3、impdp匯入資料

在預設的impdp方式下，type也是不能匯入到相同的資料庫中去的。

[oracle@oracle11g export]$ impdp scottback/scottback@wilson directory=my_dir dumpfile=scott.dmp logfile=resimp.log remap_schema=scott:scottback

Import: Release 11.2.0.1.0 - Production on Mon Jun 11 19:37:37 2012

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SCOTTBACK"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SCOTTBACK"."SYS_IMPORT_FULL_01": scottback/********@wilson directory=my_dir dumpfile=scott.dmp logfile=resimp.log remap_schema=scott:scottback

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"SCOTTBACK" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC

ORA-39083: Object type TYPE failed to create with error:

ORA-02304: invalid object identifier literal

Failing sql is:

CREATE TYPE "SCOTTBACK"."MT_TYPE" OID 'C230A55B1FC34E1DE040A8C0580017C6' as object (xm number, tchar varchar2(10));

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39117: Type needed to create table is not included in this operation. Failing sql is:

CREATE TABLE "SCOTTBACK"."MY_TABLETYPE" OF "SCOTTBACK"."MT_TYPE" OID 'C230B8AA21E527C9E040A8C058001816' OIDINDEX ( PCTFREE 10 INITRANS 2 MAXTRANS 255 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 "SYSTEM" ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "SCOTTBACK"."BASELINE_TEST" 22.90 KB 1 rows

（篇幅原因，省略部分 …..）

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SCOTTBACK"."SYS_IMPORT_FULL_01" completed with 4 error(s) at 19:37:47

注意，在預設情況下，我們匯入資料依然會遇到oid的問題。顯示的依然是type建立SQL中包括有oid資訊，引起oid衝突。進而是連帶的資料表my_tabletype不能建立。

有一個片段可以關注：

CREATE TYPE "SCOTTBACK"."MT_TYPE" OID 'C230A55B1FC34E1DE040A8C0580017C6' as object (xm number, tchar varchar2(10));

Processing object type SCHEMA_EXPORT/TABLE/TABLE

ORA-39117: Type needed to create table is not included in this operation. Failing sql is:

CREATE TABLE "SCOTTBACK"."MY_TABLETYPE" OF "SCOTTBACK"."MT_TYPE" OID 'C230B8AA21E527C9E040A8C058001816' OIDINDEX ( PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS

關聯oid相同，說明type的oid在Oracle內部是作為關聯的重要資訊使用的。

在impdp中，我們可以使用transform引數設定，要求將原有dmp檔案中oid對映重新生成。

[oracle@oracle11g export]$ impdp scottback/scottback@wilson directory=my_dir dumpfile=scott.dmp logfile=resimp2.log remap_schema=scott:scottback transform=oid:n

Import: Release 11.2.0.1.0 - Production on Mon Jun 11 19:39:07 2012

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Master table "SCOTTBACK"."SYS_IMPORT_FULL_01" successfully loaded/unloaded

Starting "SCOTTBACK"."SYS_IMPORT_FULL_01": scottback/********@wilson directory=my_dir dumpfile=scott.dmp logfile=resimp2.log remap_schema=scott:scottback transform=oid:n

Processing object type SCHEMA_EXPORT/USER

ORA-31684: Object type USER:"SCOTTBACK" already exists

Processing object type SCHEMA_EXPORT/SYSTEM_GRANT

Processing object type SCHEMA_EXPORT/ROLE_GRANT

Processing object type SCHEMA_EXPORT/DEFAULT_ROLE

Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA

Processing object type SCHEMA_EXPORT/TYPE/TYPE_SPEC

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

. . imported "SCOTTBACK"."BASELINE_TEST" 22.90 KB 1 rows

. . imported "SCOTTBACK"."DEPT" 5.937 KB 4 rows

. . imported "SCOTTBACK"."EMP" 8.992 KB 14 rows

. . imported "SCOTTBACK"."MY_TABLETYPE" 6.507 KB 1 rows

. . imported "SCOTTBACK"."SALES_QUAL" 6.007 KB 6 rows

. . imported "SCOTTBACK"."SALGRADE" 5.867 KB 5 rows

. . imported "SCOTTBACK"."BONUS" 0 KB 0 rows

. . imported "SCOTTBACK"."T" 0 KB 0 rows

. . imported "SCOTTBACK"."T1" 0 KB 0 rows

. . imported "SCOTTBACK"."T2" 0 KB 0 rows

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/TABLE/COMMENT

Processing object type SCHEMA_EXPORT/VIEW/VIEW

ORA-31684: Object type VIEW:"SCOTTBACK"."V_T1" already exists

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SCOTTBACK"."SYS_IMPORT_FULL_01" completed with 2 error(s) at 19:39:20

其中，transform取值oid:n的含義就是對oid資訊不進行載入，重新進行生成。資料表取值正確。

SQL> conn scottback/scottback@wilson;

Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0

Connected as scottback

SQL> select * from my_tabletype;

XM TCHAR

---------- ----------

1 df

4、結論

隨著Oracle功能不斷完善，很多新特性在exp/imp工具上已經不能支援。Oracle 10g下推出的Data Dump有很多功能，是我們可以進行借鑑使用的。

IMP同庫Type物件匯入報錯ORA-02304（續）

相關文章