Type是我們經常使用的資料庫物件結構。我們在實際中，可以單獨定義type型別，之後在PL/SQL程式碼或者資料表中使用。

在一個偶然的機會讓筆者發現使用Type型別在資料exp/imp中的麻煩。當我們使用exp/imp工具進行同資料庫例項（Instance）不同Schema之間資料複製時，如果Schema中有type型別，就會出現問題錯誤。

具體我們還是透過一系列的實驗進行證明。

1、實驗環境準備

我們使用10gR2作為實驗資料庫。

SQL> conn scott/tiger@ots;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as scott

SQL> select * from v$version;

BANNER

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

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

我們首先將scott使用者schema的所有物件匯出。注意，當前scott下存在一些資料type物件。

SQL> select type_name, type_oid, typecode from user_types;

TYPE_NAME TYPE_OID TYPECODE

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

CUST_ADDRESS_TYPE_NEW 0239FC5ABD78464D8D6C4D7085E2F549 OBJECT

T_REC_TEST 428A1B3C7E1E4A3CB2063B93623693EA OBJECT

T_REC_TABLE D9AFD3FAE0A54964B1684CA28C69CEED COLLECTION

T_TYP 8E294AB7CC28493A94FF82791A376379 OBJECT

N_TYP 338172B836854BAB8C26D4C27B5908F1 OBJECT

在Oracle中，每一個type都會分配出唯一的oid編號，作為一種內部標誌。下面，我們使用exp工具將scott使用者物件匯出。

D:\>exp scott/tiger@ots file=scott_20120606.dmp indexes=y rows=y compress=y cons

traints=y wner=scott

Export: Release 10.2.0.1.0 - Production on 星期三 6月 6 17:22:16 2012

連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

已匯出 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集

即將匯出指定的使用者...

. 正在匯出 pre-schema 過程物件和操作

. 正在匯出使用者 SCOTT 的外部函式庫名

. 匯出 PUBLIC 型別同義詞

. 正在匯出專用型別同義詞

. 正在匯出使用者 SCOTT 的物件型別定義

(篇幅原因，部分省略……)

成功終止匯出, 沒有出現警告。

D:\>

之後，我們建立同資料庫使用者scottback。

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、資料匯入

當我們試圖將資料匯入到相同資料庫時，出現報錯。

D:\>imp scottback/scottback@ots file=scott_20120606.dmp indexes=y rows=y constra

ints=y ignore=y fromuser=scott touser=scottback

Import: Release 10.2.0.1.0 - Production on 星期三 6月 6 17:34:21 2012

連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

經由常規路徑由 EXPORT:V10.02.01 建立的匯出檔案

警告: 這些物件由 SCOTT 匯出, 而不是當前使用者

已經完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的匯入

IMP-00017: 由於 ORACLE 錯誤 2304, 以下語句失敗:

"CREATE TYPE "T_REC_TEST" TIMESTAMP '2010-12-21:18:17:30' OID '428A1B3C7E1E4"

"A3CB2063B93623693EA' as object("

"id number);"

""

IMP-00003: 遇到 ORACLE 錯誤 2304

ORA-02304: 無效的物件識別符號文字

IMP-00017: 由於 ORACLE 錯誤 2304, 以下語句失敗:

"CREATE TYPE "T_REC_TABLE" TIMESTAMP '2010-12-21:18:17:33' OID 'D9AFD3FAE0A5"

"4964B1684CA28C69CEED' as table of t_rec_test;"

""

IMP-00003: 遇到 ORACLE 錯誤 2304

ORA-02304: 無效的物件識別符號文字

IMP-00017: 由於 ORACLE 錯誤 2304, 以下語句失敗:

"CREATE TYPE "T_TYP" TIMESTAMP '2012-03-07:10:47:03' OID '8E294AB7CC28493A94"

"FF82791A376379' as object (id number);"

""

IMP-00003: 遇到 ORACLE 錯誤 2304

ORA-02304: 無效的物件識別符號文字

IMP-00017: 由於 ORACLE 錯誤 2304, 以下語句失敗:

"CREATE TYPE "N_TYP" TIMESTAMP '2012-03-07:11:03:01' OID '338172B836854BAB8C"

"26D4C27B5908F1' as object (t_id number,t_name varchar2(10),t_addr varchar"

"2(20));"

""

IMP-00003: 遇到 ORACLE 錯誤 2304

ORA-02304: 無效的物件識別符號文字

IMP-00017: 由於 ORACLE 錯誤 2304, 以下語句失敗:

"CREATE TYPE "CUST_ADDRESS_TYPE_NEW" TIMESTAMP '2012-05-23:16:15:03' OID '02"

"39FC5ABD78464D8D6C4D7085E2F549' as object"

"(street_address varchar2"

"(40),"

"postal_code varchar2(10)"

",city varchar2(30)"

",state_province varchar2(10)"

",country_id char(2)"

");"

""

IMP-00003: 遇到 ORACLE 錯誤 2304

ORA-02304: 無效的物件識別符號文字

(篇幅原因，部分省略…..)

ORA-02270: 此列列表的唯一或主鍵不匹配

即將啟用約束條件...

成功終止匯入, 但出現警告。

從日誌資訊上，我們看到在建立type型別變數的時候，Oracle報錯2304。利用oerr工具，我們可以檢查錯誤資訊。

[oracle@bspdev ~]$ oerr ora 2304

02304, 00000, "invalid object identifier literal"

// *Cause: An attempt was made to enter an object identifier literal for

// CREATE TYPE that is either:

// - not a string of 32 hexadecimal characters

// - an object identifier that already identifies an existing

// object

// - an object identifier different from the original object

// identifier already assigned to the type

// *Action: Do not specify the object identifier clause or specify a 32

// hexadecimal-character object identifier literal that is unique

// or identical to the originally assigned object identifier. Then

// retry the operation.

從字面的情況看，是建立type的命令語句出現錯誤。從指令碼的資訊上，的確顯示的script中建立type的語句是很特殊，中間有timestamp和oid資訊。而且與原來schema中的相對應。

那麼，這個特殊的語法結構是否是檔案中特有的呢？我們使用show引數，將dmp指令碼輸出。

D:\>imp scottback/scottback@ots file=scott_20120606.dmp indexes=y rows=y constra

ints=y ignore=y show=y fromuser=scott touser=scottback log=imp.log

連線到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

經由常規路徑由 EXPORT:V10.02.01 建立的匯出檔案

警告: 這些物件由 SCOTT 匯出, 而不是當前使用者

已經完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的匯入

"CREATE TYPE "T_REC_TEST" TIMESTAMP '2010-12-21:18:17:30' OID '428A1B3C7E1E4"

"A3CB2063B93623693EA' as object("

"id number);"

""

"CREATE TYPE "T_REC_TABLE" TIMESTAMP '2010-12-21:18:17:33' OID 'D9AFD3FAE0A5"

"4964B1684CA28C69CEED' as table of t_rec_test;"

""

"CREATE TYPE "T_TYP" TIMESTAMP '2012-03-07:10:47:03' OID '8E294AB7CC28493A94"

"FF82791A376379' as object (id number);"

""

"CREATE TYPE "N_TYP" TIMESTAMP '2012-03-07:11:03:01' OID '338172B836854BAB8C"

"26D4C27B5908F1' as object (t_id number,t_name varchar2(10),t_addr varchar"

"2(20));"

看來，timestamp和oid的確是DUMP檔案的一部分。也就是說，Oracle在imp type型別的時候，要將原有的timestamp和oid連帶的轉移到新的資料環境中。

那麼，如果我們是轉移到其他資料環境下，是否有問題呢？答案是否定的，經過實驗，只要不是相同資料庫，imp操作都是正常的。

問題的關鍵在於oid，從格式上看，OID是一個類似於GUID的字串。按照GUID生成規則，GUID是不可能重複的。筆者猜測在Oracle內部，要求type型別不管schema歸屬，每一個type都必須有一個唯一的OID編號。當我們在一個資料庫中強制插入兩個相同oid的type時，系統自然報錯。

在MOS中，筆者也找到了相應的依據。

[ID 1066139.6]

In brief, if the FROMUSER's object types already exist on the target instance, errors occur because the object identifiers (OIDs) of the TOUSER's object types already exist. Within a single database instance, object identifiers (OIDs) must be unique. As a result, the error causes Import will skip the creation of relational tables with columns of the pre-existing user defined type.

3、解決方法

綜合各方面的意見，關鍵問題在於匯出的type攜帶有唯一的oid資訊，並且需要匯入到同庫schema中。在使用exp/imp的情況下，我們是沒有什麼很好的方法。最直接的做法就是將資料庫中衝突的type和相關聯的物件刪除，這樣做不是一般場景可以支援的。

在MOS中提供了一些折中方法，其中一個是在imp之前，就手工的將type物件建立好。這樣最多在imp設定ignore=y的時候報錯物件重複。

--手工建立type

SQL> conn scottback/scottback@ots;

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0

Connected as scottback

SQL> create or replace type cust_address_type_new as object

2 (

3 street_address varchar2(40),

4 postal_code varchar2(10),

5 city varchar2(30),

6 state_province varchar2(10),

7 country_id char(2)

8 )

9 ;

10 /

Type created

SQL> create or replace type n_typ as object(t_id number, t_name varchar2(10), t_addr varchar2(20));

2 /

Type created

SQL>

SQL> create or replace type t_rec_test as object(id number);

2 /

Type created

SQL> create or replace type t_typ as object(id number);

2 /

Type created

SQL>

SQL> create or replace type t_rec_table as table of t_rec_test;

2 /

Type created

--匯入操作；

D:\>imp scottback/scottback@ots file=scott_20120606.dmp indexes=y rows=y constraints=y ignore=y fromuser=scott touser=scottback log=res.log

警告: 這些物件由 SCOTT 匯出, 而不是當前使用者

已經完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的匯入

IMP-00061: 警告: 物件型別 "SCOTTBACK"."T_REC_TEST" 已經以不同識別符號存在

"CREATE TYPE "T_REC_TEST" TIMESTAMP '2010-12-21:18:17:30' OID '428A1B3C7E1E4"

"A3CB2063B93623693EA' as object("

"id number);"

""

IMP-00061: 警告: 物件型別 "SCOTTBACK"."T_REC_TABLE" 已經以不同識別符號存在

"CREATE TYPE "T_REC_TABLE" TIMESTAMP '2010-12-21:18:17:33' OID 'D9AFD3FAE0A5"

"4964B1684CA28C69CEED' as table of t_rec_test;"

""

IMP-00061: 警告: 物件型別 "SCOTTBACK"."T_TYP" 已經以不同識別符號存在

"CREATE TYPE "T_TYP" TIMESTAMP '2012-03-07:10:47:03' OID '8E294AB7CC28493A94"

"FF82791A376379' as object (id number);"

""

IMP-00061: 警告: 物件型別 "SCOTTBACK"."N_TYP" 已經以不同識別符號存在

"CREATE TYPE "N_TYP" TIMESTAMP '2012-03-07:11:03:01' OID '338172B836854BAB8C"

"26D4C27B5908F1' as object (t_id number,t_name varchar2(10),t_addr varchar"

"2(20));"

""

IMP-00061: 警告: 物件型別 "SCOTTBACK"."CUST_ADDRESS_TYPE_NEW" 已經以不同識別符號存在

"CREATE TYPE "CUST_ADDRESS_TYPE_NEW" TIMESTAMP '2012-05-23:16:15:03' OID '02"

"39FC5ABD78464D8D6C4D7085E2F549' as object"

"(street_address varchar2"

"(40),"

"postal_code varchar2(10)"

",city varchar2(30)"

",state_province varchar2(10)"

",country_id char(2)"

");"

""

. . 正在匯入表 "A"匯入了 1 行

IMP-00063: 警告: 跳過表 "SCOTTBACK"."ADDRESS_TABLE", 因為無法建立物件型別 "SCOTTBACK"."CUST_ADDRESS_TYPE_NEW" 或它具有不同的識別符號

. . 正在匯入表 "B"匯入了 2 行

. . 正在匯入表 "BONUS"匯入了 0 行

. . 正在匯入表 "BO_TEST"匯入了 0 行

. . 正在匯入表 "CHAINED_ROWS"

注: 表包含 ROWID 列, 其值可能已廢棄匯入了 0 行

. . 正在匯入表 "CHILD"匯入了 0 行

. . 正在匯入表 "CURSOR"匯入了 3 行

IMP-00063: 警告: 跳過表 "SCOTTBACK"."CUSTOMER_ADDRESSES", 因為無法建立物件型別 "SCOTTBACK"."CUST_ADDRESS_TYPE_NEW" 或它具有不同的識別符號

這樣做的確可以避免報錯。但是後果也是存在的，如果這些匯入的type存在依賴物件。如資料表列、儲存過程程式碼依賴於type。雖然手工建立了原有type，但是這些物件也不會使用建立好的type物件。筆者猜測這就是oid的作用。

SQL> select name, type, DEPENDENCY_TYPE from user_dependencies where REFERENCED_NAME in (select type_name from user_types);

NAME TYPE DEPENDENCY_TYPE

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

CUSTOMER_ADDRESSES TABLE REF

ADDRESS_TABLE TABLE HARD

F_SPILE FUNCTION HARD

T_REC_TABLE TYPE HARD

F_SPILE FUNCTION HARD

N_T TABLE HARD

6 rows selected

4、結論

在使用type中，一定要注意可能引起的imp/exp匯出匯入問題。

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

相關文章