最近有个小数据库中某用户下的所有数据需要迁移到另外一个,现在先模拟下:
数据量并不是很大的情况下,这里使用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.