有一個在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.
但是我想先把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.
全站熱搜
留言列表
