EXP/IMP数据迁移
EXP/IMP数据迁移
最近有个小数据库中某用户下的所有数据需要迁移到另外一个，现在先模拟下：
数据量并不是很大的情况下，这里使用exp导出该用户下的所有数据对象：
[sql]
[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.
然后直接导入到目标库的目标用户下，进行的还是比较顺利的，在这过程中可能会报无法创建文件等错误，请注意权限问题：
[sql]
[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.
[sql]
如果只要导出数据库结构而不导出数据，则（加参数rows=n）：
[sql]
[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.
本站文章为和通数据库网友分享或者投稿，欢迎任何形式的转载，但请务必注明出处.
同时文章内容如有侵犯了您的权益，请联系QQ：970679559，我们会在尽快处理。