Oracle_利用exp和imp迁移数据

测试环境  redhat 5.5   oracle 11g

 

先在源端导出，命令及结果如下：

[oracle@localhost ~]$ exp der322/espace owner=der322 file=wox1455.dmp log=wox1455.log buffer=6000000

Export: Release 11.2.0.1.0 - Production on Wed Nov 16 21:05:28 2016


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

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set
server uses AL32UTF8 character set (possible charset conversion)


About to export specified users ...
. exporting pre-schema procedural objects and actions
. exporting foreign function library names for user DER322 
. exporting PUBLIC type synonyms
. exporting private type synonyms
. exporting object type definitions for user DER322 
About to export DER322's objects ...
. exporting database links
. exporting sequence numbers
. exporting cluster definitions
. about to export DER322's tables via Conventional Path ...
. . exporting table                         TEST01          6 rows exported
. . exporting table                       WOXTOPIC      11343 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.

 

在到目标端创建用户（可以不用建表空间）

create user der322 identified by espace;
grant dba to der322;

然后执行以下命令：

[oracle@localhost ~]$ imp der322/espace fromuser=der322 touser=der322 file=wox1455.dmp log=wox1455.log ignore=y


Import: Release 11.2.0.1.0 - Production on Wed Nov 16 15:28:58 2016

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

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


Export file created by EXPORT:V11.02.00 via conventional path
import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses AL32UTF8 character set (possible charset conversion)
. . importing table                       "TEST01"          6 rows imported
. . importing table                     "WOXTOPIC"      11343 rows imported
Import terminated successfully without warnings.

 

成功搞定！

 

优缺点：优点是可以跨平台使用;缺点是停机时间长，停机时间为从exp到网络传输到新库，再加上imp的时间。

----------------------------

针对某个表的exp/imp  (未测试）

expdp system/oracle directory=dump dumpfile=srctest.dmp logfile=src.test.log tables=datasrc.test1 flashback_scn=1378813

impdp system/oracle directory=dump dumpfile=srctest.dmp logfile=src.test.log tables=datasrc.test1 remap_schema=datasrc:datatgt

 

 

 

2019.1.29 因为需要，尝试12c，pluggable下面的exp,imp操作。

exp kt_csearch/kt_sso_2018720@192.168.58.216:1521/ORCLpdb owner=kt_csearch file=/opt/oracle/g5/search216_0128.dmp
log=/opt/oracle/g5/search216_0128.log buffer=6000000

ALTER SESSION SET container=ORCLpdb;

--drop user kt_csearch cascade;
--drop tablespace tbs_kt_csearch including contents and datafiles;

CREATE TABLESPACE tbs_kt_csearch DATAFILE '/opt/oracle/g5/dbf/kt_csearch01.dbf' SIZE 100m AUTOEXTEND ON;
alter tablespace tbs_kt_csearch add datafile '/opt/oracle/g5/dbf/kt_csearch02.dbf' size 100m autoextend on;
alter tablespace tbs_kt_csearch add datafile '/opt/oracle/g5/dbf/kt_csearch03.dbf' size 100m autoextend on;

CREATE USER kt_csearch IDENTIFIED BY kt_sso_2018720 DEFAULT TABLESPACE tbs_kt_csearch;

grant connect to kt_csearch;
grant resource to kt_csearch;
ALTER USER  kt_csearch QUOTA UNLIMITED ON tbs_kt_csearch;

--210
imp kt_csearch/kt_sso_2018720@192.168.58.210:1521/ORCLpdb fromuser=kt_csearch touser=kt_csearch
file=/opt/oracle/g5/search216_0128.dmp log=/opt/oracle/g5/search216_0128.log ignore=y