Oracle数据迁移简述

exp导出

命令行

1.多表导出

mkdir /u02
[oracle@OCPLHR u02]$  exp scott/oracle file=/u02/scott1.dmp tables=emp,dept;

2.按用户导出

[oracle@OCPLHR ~]$ exp scott/oracle file=/u02/scottu.dmp owner=scott

3.按多用户导出

[oracle@OCPLHR u02]$ exp system/oracle file=/u02/scottU.dmp owner=scott,lyh log=/tmp/expscott.log

其中，log=/tmp/expscott.log为导出过程的记录日志

4.sysdba

[oracle@OCPLHR u02]$ exp \'sys/oracle as sysdba\' file=/u02/uscott.dmp owner=scott

5.文件大，导出数据文件多个

[oracle@OCPLHR u02]$ exp scott/oracle file=/u02/s01.dmp,/u02/s02.dmp filesize=1g tables=emp,dept log=/tmp/emp01.log

6.全库

[oracle@OCPLHR u02]$ exp \'sys/oracle as sysdba\' file=/u02/full1.dmp,/u02/full2.dmp filesize=100m full=y

全库导出，必须用sysdba等dba权限的用户

参数文件

按表导出

脚本：
userid=scott/oracle
file=/u02/ss01.tmp
tables=emp,dept

[oracle@OCPLHR u02]$ exp parfile=p1.txt

按表导出，导出其他用户的表

userid=system/oracle
file=/u02/ss03.dmp
tables=scott.emp,scott.dept

[oracle@OCPLHR u02]$ exp parfile=p2.txt

按用户导出其他的表

userid=system/oracle
file=/u02/scottexp.dmp
owner=scott

[oracle@OCPLHR u02]$ exp parfile=p3.txt

按条件导出(比如时间)

userid=scott/oracle
file=emp10-20.dmp
tables=emp
query='where deptno in (10,20)'


[oracle@OCPLHR u02]$ exp parfile=p4.txt

Imp导入

命令行

[oracle@OCPLHR u02]$ imp scott/oracle file=/u02/scott.dmp tables=emp,dept

参数文件

userid=scott/oracle
file=/u02/scott.dmp
tables=emp,dept

imp parfile=im.txt

表存在，但部分内容清除

[oracle@OCPLHR u02]$ imp scott/oracle file=/u02/scott.dmp tables=emp ignore=y

导入表时更换表的用户

[oracle@OCPLHR u02]$ imp system/oracle fromuser=scott touser=lyh file=/u02/scott.dmp tables=emp,dept

Expdp/impdp

Expdp

新建逻辑目录
最好以system等管理员创建逻辑目录，Oracle不会自动创建实际的物理目录“/u02”（务必手动创建此目录），仅仅是进行定义逻辑路径exp；

[oracle@OCPLHR u02]$ sqlplus / as sysdba

SYS@OCPLHR1> create directory exp as '/u02/';

Directory created.

SYS@OCPLHR1> grant read,write on directory exp to scott;

Grant succeeded.

查看管理员目录
同时查看操作系统是否存在该目录，因为oracle并不关心该目录是否存在，假如不存在，则出错

sql>select * from dba_directories;

导出制定表

[oracle@OCPLHR u02]$ expdp scott/oracle@OCPLHR1 tables=emp,dept dumpfile=expdp.dmp directory=exp;

导出用户及其对象

[oracle@OCPLHR u02]$ expdp scott/oracle@OCPLHR1 schemas=scott dumpfile=expdp2.dmp directory=exp;

按查询条件导出

[oracle@OCPLHR u02]$  expdp scott/oracle@OCPLHR1 directory=exp dumpfile=expdp3.dmp tables=emp query=\'where DEPTNO=20\';

按表空间导出

[oracle@OCPLHR u02]$ expdp system/oracle@OCPLHR1 directory=exp dumpfile=tablespace.dmp tablespaces=temp,example;

全库导出

[oracle@OCPLHR u02]$ expdp system/oracle@OCPLHR1 directory=exp dumpfile=full.dmp full=y;

Impdp

在正式导入数据前，要先确保要导入的用户已存在，如果没有存在，请先用下述命令进行新建用户

--创建表空间
SYS@OCPLHR1> create tablespace tb_name datafile '/u02/tb_name.dbf' size 1024m AUTOEXTEND ON;

--创建用户
      create user user_name identified by A123456a default tablespace tb_name temporary tablespace TEMP;
--给用户授权
sql>grant read,write on directory dump_dir to user_name;
sql>grant dba,resource,unlimited tablespace to user_name;

导入用户

（从用户scott导入到用户scott）
[oracle@OCPLHR u02]$ impdp scott/oracle@OCPLHR1 directory=exp dumpfile=expdp.dmp tables=emp,dept;

导入表

（从scott用户中把表dept和emp导入到system用户中）
[oracle@OCPLHR u02]$ impdp system/oracle@OCPLHR1 directory=exp dumpfile=expdp.dmp tables=scott.dept,scott.emp remap_schema=scott:lyh;

导入表空间

impdp system/oracle@OCPLHR1 directory=exp dumpfile=tablespace.dmp tablespaces=example;

导入数据库

补充知识点：删除数据库

以上与本节内容无关

[oracle@OCPLHR u02]$ impdp system/oracle@OCPLHR1 directory=exp dumpfile=full.dmp full=y;

追加数据

[oracle@OCPLHR u02]$ impdp system/oracle@OCPLHR1 directory=exp dumpfile=expdp.dmp schemas=emp table_exists_action=append
[oracle@OCPLHR ~]$ impdp scott/oracle@OCPLHR1 directory=exp dumpfile=expdp.dmp tables=emp table_exists_action=append

表存在，替换

[oracle@OCPLHR u02]$ impdp system/oracle@OCPLHR1 directory=exp dumpfile=expdp.dmp schemas=scott table_exists_action=replace
[oracle@OCPLHR u02]$ impdp system/oracle@OCPLHR1 directory=exp dumpfile=expdp.dmp table_exists_action=replace

表存在，删除旧表

impdp system/oracle@OCPLHR1 directory=exp dumpfile=expdp.dmp schemas=system table_exists_action=truncate
[oracle@OCPLHR u02]$ impdp system/oracle@OCPLHR1 directory=exp dumpfile=expdp.dmp table_exists_action=truncate

参数文件

[oracle@OCPLHR ~]$ vim /u02/dp.txt

userid=scott/oracle
dumpfile=expdp.dmp
directory=exp
table_exists_action=truncate

[oracle@OCPLHR u02]$ impdp parfile=/u02/dp.txt

表空间传输（transport tablespace）

举例：将tt从OCPLHR1传输到OCPLHR2
创建一个表空间tt
SYS@OCPLHR1> create tablespace tt datafile '/u01/app/oracle/oradata/OCPLHR1/tt.dbf' size 10m;

Tablespace created.

SYS@OCPLHR1> create user tt identified by tt default tablespace tt;

User created.

SYS@OCPLHR1> grant connect,resource to tt;

Grant succeeded.

SYS@OCPLHR1> create table tt.e as select * from scott.emp;

Table created. 

在OCPLHR1库上将tt导出并备份数据文件

SYS@OCPLHR1> alter tablespace tt read only;

Tablespace altered.

[oracle@OCPLHR u02]$  exp \'sys/oracle as sysdba\' file=/u02/tt.dmp transport_tablespace=y tablespaces=tt

[oracle@OCPLHR OCPLHR2]$ cp /u01/app/oracle/oradata/OCPLHR1/tt.dbf /u01/app/oracle/OCPLHR2

SYS@OCPLHR1> alter tablespace tt read write;

[oracle@OCPLHR u02]$ imp \'sys/oracle as sysdba\' file=/u02/tt.dmp transport_tablespace=y tablespaces=tt datafiles=/u01/app/oracle/OCPLHR2/tt.dbf