EXP/IMP数据迁移

最新推荐文章于 2021-07-29 15:56:01 发布

原创最新推荐文章于 2021-07-29 15:56:01 发布 · 1.4k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#ORACLE #IMP #EXP

Oracle 专栏收录该内容

90 篇文章

订阅专栏

最近有个小数据库中某用户下的所有数据需要迁移到另外一个，现在先模拟下：

数据量并不是很大的情况下，这里使用exp导出该用户下的所有数据对象：

[lubinsu@localhost orcl]$ exp lubinsu/lubinsu@orcl_03 file=/home/lubinsu/orcl/lubinsu.dmp log=/home/lubinsu/orcl/lubinsu.log owner=lubinsu compress=n
Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user LUBINSU 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user LUBINSU 
About to export LUBINSU's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export LUBINSU's tables via Conventional Path ...
. . exporting table                        AREA_TM      33105 rows exported
. . exporting table                  BSS_PO_SPEC_D      26570 rows exported
. . exporting table            CRM_BASE_BUSI_ORDER      10000 rows exported
. . exporting table                           DEPT          4 rows exported
. . exporting table                            EMP         14 rows exported
. . exporting table                       GRID_MSG     156588 rows exported
. . exporting table                      JOB_PARMS          0 rows exported
. . exporting table                 LATN_AREA_INFO         14 rows exported
. . exporting table                     MANAGER_TM      66140 rows exported
. . exporting table                   MANAGER_TM_T      65994 rows exported
. . exporting table         PO_SPEC_CATE_TREE_RELA        578 rows exported
. . exporting table               PROCEDURE_RESULT         15 rows exported
. . exporting table                       SERV_MSG
. . exporting partition                     P_PROVINCE          0 rows exported
. . exporting partition                           P_NJ      10008 rows exported
. . exporting partition                           P_WX      10008 rows exported
. . exporting partition                           P_ZJ      10008 rows exported
. . exporting partition                           P_SZ      10008 rows exported
. . exporting partition                           P_NT      10008 rows exported
. . exporting partition                           P_YZ      10008 rows exported
. . exporting partition                           P_YC      10008 rows exported
. . exporting partition                           P_XZ      10008 rows exported
. . exporting partition                           P_HA      10008 rows exported
. . exporting partition                          P_LYG      10008 rows exported
. . exporting partition                           P_CZ      10008 rows exported
. . exporting partition                           P_TZ      10008 rows exported
. . exporting partition                           P_SQ      10008 rows exported
. . exporting table                 STAT_PROCEDURE        163 rows exported
. . exporting table         TEACHER_VPN_STAT_DAILY       2377 rows exported
. . exporting table       TEACHER_VPN_STAT_DAILY_T         65 rows exported
. . exporting table                            TT4      49880 rows exported
. . exporting table                          T_SCN         18 rows exported
. . exporting table                         T_TEST     393800 rows exported
. . exporting table                       T_TEST_1      49894 rows exported
. . exporting table                       T_TEST_2      49895 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.

然后直接导入到目标库的目标用户下，进行的还是比较顺利的，在这过程中可能会报无法创建文件等错误，请注意权限问题：

[lubinsu@localhost orcl]$ imp lubinsu/lubinsu file=/home/lubinsu/orcl/lubinsu.dmp log=/home/lubinsu/orcl/lubinsu2.log fromuser=lubinsu touser=lubinsu

Import: Release 10.2.0.1.0 - Production on Sat Jun 15 15:51:01 2013

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. . importing table                      "AREA_TM"      33105 rows imported
. . importing table                "BSS_PO_SPEC_D"      26570 rows imported
. . importing table          "CRM_BASE_BUSI_ORDER"      10000 rows imported
. . importing table                         "DEPT"          4 rows imported
. . importing table                          "EMP"         14 rows imported
. . importing table                     "GRID_MSG"     156588 rows imported
. . importing table                    "JOB_PARMS"          0 rows imported
. . importing table               "LATN_AREA_INFO"         14 rows imported
. . importing table                   "MANAGER_TM"      66140 rows imported
. . importing table                 "MANAGER_TM_T"      65994 rows imported
. . importing table       "PO_SPEC_CATE_TREE_RELA"        578 rows imported
. . importing table             "PROCEDURE_RESULT"         15 rows imported
. . importing partition        "SERV_MSG":"P_PROVINCE"          0 rows imported
. . importing partition              "SERV_MSG":"P_NJ"      10008 rows imported
. . importing partition              "SERV_MSG":"P_WX"      10008 rows imported
. . importing partition              "SERV_MSG":"P_ZJ"      10008 rows imported
. . importing partition              "SERV_MSG":"P_SZ"      10008 rows imported
. . importing partition              "SERV_MSG":"P_NT"      10008 rows imported
. . importing partition              "SERV_MSG":"P_YZ"      10008 rows imported
. . importing partition              "SERV_MSG":"P_YC"      10008 rows imported
. . importing partition              "SERV_MSG":"P_XZ"      10008 rows imported
. . importing partition              "SERV_MSG":"P_HA"      10008 rows imported
. . importing partition             "SERV_MSG":"P_LYG"      10008 rows imported
. . importing partition              "SERV_MSG":"P_CZ"      10008 rows imported
. . importing partition              "SERV_MSG":"P_TZ"      10008 rows imported
. . importing partition              "SERV_MSG":"P_SQ"      10008 rows imported
. . importing table               "STAT_PROCEDURE"        163 rows imported
. . importing table       "TEACHER_VPN_STAT_DAILY"       2377 rows imported
. . importing table     "TEACHER_VPN_STAT_DAILY_T"         65 rows imported
. . importing table                          "TT4"      49880 rows imported
. . importing table                        "T_SCN"         18 rows imported
. . importing table                       "T_TEST"     393800 rows imported
. . importing table                     "T_TEST_1"      49894 rows imported
. . importing table                     "T_TEST_2"      49895 rows imported
Import terminated successfully without warnings.


如果只要导出数据库结构而不导出数据，则（加参数rows=n）：
[lubinsu@localhost orcl]$ exp lubinsu/lubinsu@orcl_03 file=/home/lubinsu/orcl/lubinsu.dmp rows=n log=lubinsu_exp.log

Export: Release 10.2.0.1.0 - Production on Sat Jun 15 16:14:41 2013

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


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported

About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user LUBINSU 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user LUBINSU 
About to export LUBINSU's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export LUBINSU's tables via Conventional Path ...
. . exporting table                        AREA_TM
. . exporting table                  BSS_PO_SPEC_D
. . exporting table            CRM_BASE_BUSI_ORDER
. . exporting table                           DEPT
. . exporting table                            EMP
. . exporting table                       GRID_MSG
. . exporting table                      JOB_PARMS
. . exporting table                 LATN_AREA_INFO
. . exporting table                     MANAGER_TM
. . exporting table                   MANAGER_TM_T
. . exporting table         PO_SPEC_CATE_TREE_RELA
. . exporting table               PROCEDURE_RESULT
. . exporting table                       SERV_MSG
. . exporting table                 STAT_PROCEDURE
. . exporting table         TEACHER_VPN_STAT_DAILY
. . exporting table       TEACHER_VPN_STAT_DAILY_T
. . exporting table                            TT4
. . exporting table                          T_SCN
. . exporting table                         T_TEST
. . exporting table                       T_TEST_1
. . exporting table                       T_TEST_2
. 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.