Oracle 9i export某一個TABLE的資料,再import到11g

有一個在9i的tmp_ccc表格,想要移轉到11g,當然可以用DB_LINK將兩台DB連接後再insert資料過去。
但是我想先把tmp_ccc的資料備份出來,等有需要時再匯入11g。

在9i的OS下,執行exp匯出資料檔
$ exp test01/XXXXX file=/home/oraprod/exp_test.dmp buffer=65400 feedback=100 tables=tmp_ccc 

Export: Release 9.2.0.7.0 - Production on Wed May 14 16:14:30 2014

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production
Export done in UTF8 character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                        TMP_CCC
..........
                                                         1018 rows exported
Export terminated successfully without warnings.


總共有1018筆資料匯出成功,接著我利用FTP的方式把資料檔(exp_test.dmp)傳到11g的DB上。

在11g的OS下,執行imp匯入資料檔,因為11g已有temp_ccc的表格,所以加上data_only參數只匯入資料。
$ imp test01/XXXXX file=/export/home/testora/exp_test.dmp  feedback=100 tables=tmp_ccc data_only=Y

Import: Release 11.2.0.4.0 - Production on Wed May 14 16:29:35 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V09.02.00 via conventional path
import done in UTF8 character set and AL16UTF16 NCHAR character set
. importing CUSADMIN's objects into CUSADMIN
. . importing table                      "TMP_CCC"
IMP-00058: ORACLE error 904 encountered
ORA-00904: "F": invalid identifier
Import terminated successfully with warnings.

結果出現ORA-00904的錯誤,比較兩方的表格之後,發現9i的tmp_ccc多了一個欄位「F」,是後來新增的。
所以在11g的DB下,也新增欄位如下:
SQL> alter table test01.tmp_ccc add f varchar(100);


回到11g的OS底上,重新執行imp匯入資料,這次就成功了。
$ imp test01/XXXXX file=/export/home/testora/exp_test.dmp buffer=65400 feedback=100 tables=tmp_ccc data_only=Y

Import: Release 11.2.0.4.0 - Production on Wed May 14 16:52:13 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V09.02.00 via conventional path
import done in UTF8 character set and AL16UTF16 NCHAR character set
. importing CUSADMIN's objects into CUSADMIN
. . importing table                      "TMP_CCC"
..........
                                                         1018 rows imported
Import terminated successfully without warnings.

Oracle Data Gurad Archive Gap