【imp】使用imp工具遷移資料時迂迴地排除特定表的匯入

secooler發表於2010-08-29
原文網址 : http://blog.itpub.net/519536/viewspace-672170/
如果遷移資料時使用的是Oracle的imp工具，如何排除特定表的匯入，滿足個性化需求呢？imp工具沒有impdp工具那樣靈活，這裡給出一個迂迴的辦法：先建立同名的資料庫物件，然後忽略匯入過程中的錯誤即可。簡單演示一下，供參考。其中的一些細節同樣值得思考和注意。

1.環境準備
1）建立使用者sec並授權
sys@ora10g> drop user sec cascade;
sys@ora10g> create user sec identified by sec default tablespace TBS_SEC_D;
sys@ora10g> grant dba to sec;

2）在sec使用者下建立三張表，為每張表中初始化一條資料
sys@ora10g> conn sec/sec
sec@ora10g> create table t1 (x int);
sec@ora10g> create table t2 (x int);
sec@ora10g> create table t3 (x int);
sec@ora10g> insert into t1 values(1);
sec@ora10g> insert into t2 values(2);
sec@ora10g> insert into t3 values(3);
sec@ora10g> commit;

sec@ora10g> select * from tab;

TNAME                          TABTYPE  CLUSTERID
------------------------------ ------- ----------
T2                             TABLE
T1                             TABLE
T3                             TABLE

2.使用exp工具生成sec使用者的備份檔案
ora10g@secdb /home/oracle$ exp sec/sec file=sec.dmp

Export: Release 10.2.0.3.0 - Production on Mon Aug 29 08:53:55 2010

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options
Export done in AL32UTF8 character set and UTF8 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user SEC
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user SEC
About to export SEC's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export SEC's tables via Conventional Path ...
. . exporting table                             T1          1 rows exported
. . exporting table                             T2          1 rows exported
. . exporting table                             T3          1 rows exported
. exporting synonyms
. exporting views
. exporting stored procedures
. exporting operators
. exporting referential integrity constraints
. exporting triggers
. exporting indextypes
. exporting bitmap, functional and extensible indexes
. exporting posttables actions
. exporting materialized views
. exporting snapshot logs
. exporting job queues
. exporting refresh groups and children
. exporting dimensions
. exporting post-schema procedural objects and actions
. exporting statistics
Export terminated successfully without warnings.

3.刪除sec使用者下的表，同時建立一張t1表
我們的目標是在imp匯入資料的過程中跳過t1。
sec@ora10g> drop table t1 purge;
sec@ora10g> drop table t2 purge;
sec@ora10g> drop table t3 purge;
sec@ora10g> select * from cat;

no rows selected

建立一個表t1
sec@ora10g> create table t1 (x int);

Table created.

4.使用imp工具完成匯入，確認t1表未被匯入
1）匯入操作
ora10g@secdb /home/oracle$ imp sec/sec file=sec.dmp full=y

Import: Release 10.2.0.3.0 - Production on Mon Aug 29 08:56:24 2010

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and UTF8 NCHAR character set
. importing SEC's objects into SEC
IMP-00015: following statement failed because the object already exists:
 "CREATE TABLE "T1" ("X" NUMBER(*,0))  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTR"
 "ANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEF"
 "AULT)                        LOGGING NOCOMPRESS"
. . importing table                           "T2"          1 rows imported
. . importing table                           "T3"          1 rows imported
Import terminated successfully with warnings.

可見，此時在建立t1表的時候報錯，此時t1的資料同樣沒有被匯入，即實現了跳過t1表匯入的目的。

2）檢查資料匯入結果
sec@ora10g> select * from t1;

no rows selected

sec@ora10g> select * from t2;

         X
----------
         2

sec@ora10g> select * from t3;

         X
----------
         3

OK，滿足了我們的目標。

5.注意事項
這裡需要注意，如果使用的是預先建立資料庫物件是表t1，同時t1表與待排除匯入的t1表結構相同或相近（例如建立t1表語句為create table t (a varchar2(10), b varchar2(10));），則不可使用ignore=y選項，否則t1表中的資料仍然會被匯入。實驗演示如下。

1）刪除t2和t3表，保留t1這個空表
sec@ora10g> drop table t2 purge;
sec@ora10g> drop table t3 purge;

2）使用具有ignore=y選項的imp命令完成匯入
ora10g@secdb /home/oracle$ imp sec/sec file=sec.dmp full=y ignore=y

Import: Release 10.2.0.3.0 - Production on Mon Aug 29 08:59:29 2010

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and UTF8 NCHAR character set
. importing SEC's objects into SEC
. . importing table                           "T1"          1 rows imported
. . importing table                           "T2"          1 rows imported
. . importing table                           "T3"          1 rows imported
Import terminated successfully without warnings.

該結果已說明，在使用ignore=y選項時，即使表t1已經存在，依然可以完成資料的匯入，這與我們的目標相違背。

6.避免資料被誤匯入的方法
我們可以建立其他以t1命名的資料庫物件來規避上文中提到的問題。例如我們建立一個叫做t1的資料庫檢視。
sec@ora10g> create view t1 as select * from dual;

View created.

此時即使我們使用ignore=y選項，表t1的資料也不會被匯入。
ora10g@secdb /home/oracle$ imp sec/sec file=sec.dmp full=y ignore=y

Import: Release 10.2.0.3.0 - Production on Mon Aug 29 09:10:24 2010

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining Scoring Engine options

Export file created by EXPORT:V10.02.01 via conventional path
import done in AL32UTF8 character set and UTF8 NCHAR character set
. importing SEC's objects into SEC
. . importing table                           "T1"
IMP-00058: ORACLE error 904 encountered
ORA-00904: "X": invalid identifier
. . importing table                           "T2"          1 rows imported
. . importing table                           "T3"          1 rows imported
Import terminated successfully with warnings.

7.小結
在某些必須使用imp工具進行遷移資料時，如果能夠很好的利用文中提到的方法，可以很高效地滿足遷移資料過程中個性化的需求。
很多時候我們可以換一個思路解決問題，只要綜合成本最低就是好方法。

歡迎大家分享自己的方法。

Good luck.

secooler
10.08.29

-- The End --

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

相關文章