oracle数据泵的使用

逻辑备份：EXP / IMP

1.表模式：
exp parfile=c:\exp.txt
----------------------
userid=scott/tiger
tables=emp
#query='where deptno=30'
file=c:\exp_emp.dmp
buffer=1000000
feedback=5
log=c:\exp_emp.log

imp parfile=c:\imp.txt
----------------------
userid=scott/tiger
tables=emp
file=c:\exp_emp.dmp
buffer=1000000
feedback=5
log=c:\imp_emp.log

2.用户模式：
exp parfile=c:\exp.txt
----------------------
userid=scott/tiger
owner=scott
file=c:\exp_scott.dmp
buffer=1000000
feedback=5
log=c:\exp_scott.log

drop user scott cascade;
create user scott identified by tiger;
grant connect,resource to scott;

imp parfile=c:\imp.txt
-----------------------
userid=scott/tiger
file=c:\exp_scott.dmp
full=y
buffer=1000000
feedback=5
log=c:\imp_scott.log

跨用户导入数据：
create user test identified by test;
grant connect,resource to test;

imp parfile=c:\imp.txt
-----------------------
userid=system/oracle
file=c:\exp_scott.dmp
fromuser=scott
touser=test
tables=dept,emp
buffer=1000000
feedback=5
log=c:\imp_scott.log

3.表空间模式:
exp parfile=c:\exp.txt
----------------------
userid=system/oracle
tablespaces=users
file=c:\exp_users.dmp
buffer=1000000
feedback=5
log=c:\exp_users.log

drop tablespace users including contents and datafiles;
create tablespace users datafile 'D:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF' size 10m;

imp parfile=c:\imp.txt
-----------------------
userid=system/oracle
file=c:\exp_users.dmp
full=y
buffer=1000000
feedback=5
log=c:\imp_users.log

4.传输表空间模式:
create tablespace data53 datafile 'f:\data53.dbf' size 5m;
create user u1 identified by u1 default tablespace data53;
grant connect,resource to u1;
conn u1/u1
create table t1 (id number(4),name varchar2(20));
insert into t1 values (1,'ALVIN');
commit;

conn / as sysdba
alter tablespace data53 read only;

exp parfile=c:\exp.txt
----------------------
userid='sys/oracle as sysdba'
tablespaces=data53
transport_tablespace=y
file=c:\trans_data53.dmp
buffer=1000000
log=c:\trans_data53.log

将f:\data53.dbf和c:\trans_data53.dmp两个文件拷贝到远程节点
创建U1用户：
create user u1 identified by u1;
grant connect,resource to u1;

imp parfile=c:\imp.txt
-----------------------
userid='sys/oracle as sysdba'
file=c:\trans_data53.dmp
tablespaces=data53
transport_tablespace=y
datafiles='f:\data53.dbf'
log=c:\trans_data53.log

conn / as sysdba
alter tablespace data53 read write;

5.全库模式:
exp parfile=c:\exp.txt
----------------------
userid=system/oracle
full=y
filesize=50m
file=(c:\exp_full_1.dmp,c:\exp_full_2.dmp)
buffer=10000000
feedback=10000
log=c:\exp_full.log

imp parfile=c:\imp.txt
-----------------------
userid=system/oracle
full=y
filesize=50m
file=(c:\exp_full_1.dmp,c:\exp_full_2.dmp)
buffer=10000000
feedback=10000
log=c:\imp_full.log

------------------------------------------------------------------------
数据泵(只能在ORACLE服务端使用, 不能在客户端使用):
SELECT * FROM ALL_DIRECTORIES;
create directory MY_DIR as 'c:\';
grant read,write on DIRECTORY MY_DIR to scott;
select PRIVILEGE,TABLE_NAME from dba_tab_privs where GRANTEE='SCOTT';

只导出对象定义（METADATA_ONLY）
EXPDP parfile=c:\expdp.txt
----------------------------
USERID=uxin/uxin
DIRECTORY=expbak
job_name=pump_tab_e
COnTENT=METADATA_ONLY
DUMPFILE=e.dmp
TABLES=e

只导出对象数据（DATA_ONLY）
EXPDP parfile=c:\expdp.txt
----------------------------
USERID=scott/tiger
DIRECTORY=MY_DIR
job_name=pump_tab_e
COnTENT=DATA_ONLY
DUMPFILE=e.dmp
TABLES=e

impdp parfile=c:\impdp.txt
----------------------------
userid=scott/tiger
DIRECTORY=MY_DIR
DUMPFILE=E.DMP
TABLES=e

导出对象定义和数据（all）
EXPDP parfile=c:\expdp.txt
----------------------------
USERID=scott/tiger
DIRECTORY=MY_DIR
job_name=pump_tab_e
COnTENT=all
DUMPFILE=e.dmp
TABLES=e,d

impdp parfile=c:\impdp.txt
----------------------------
userid=scott/tiger
DIRECTORY=MY_DIR
DUMPFILE=E.DMP

导出用户所有数据（连接的用户）
EXPDP parfile=c:\expdp.txt
----------------------------
USERID=scott/tiger
DIRECTORY=MY_DIR
job_name=pump_tab_e
COnTENT=all
DUMPFILE=scott.dmp

导出用户所有数据（并行模式）
EXPDP parfile=c:\expdp.txt
----------------------------
USERID=system/oracle
DIRECTORY=MY_DIR
PARALLEL=2
job_name=pump_tab_e
COnTENT=all
DUMPFILE=scott.dmp
schemas=scott

-----------------------------------------------------
include/exclude 例子：
include=table:"in('DB','TS')"
或者include=table:"like '%E%'"
或者include=function,package,procedure,table:"='EMP'"
或者exclude=SEQUENCE,TABLE:"IN ('EMP','DEPT')"

EXPDP parfile=c:\expdp.txt
-----------------------------------------------------
USERID=scott/tiger
DIRECTORY=MY_DIR
job_name=pump_tab_e
COnTENT=all
DUMPFILE=e.dmp
include=table:"like 'E%'"
-----------------------------------------------------
在同一个数据库中把一个schema中所有的对象复制到另一个schema中。

--1.创建连接自己的database link:

create public database link system_self connect to system identified by "oracle" using 'orcl';

数据库链接已创建。

--2.复制hr schema到test schema:

impdp system/oracle network_link=system_self schemas=hr remap_schema=hr:test

将一个用户的数据导入到另一个用户中
impdp "'scott/tiger as sysdba'" directory=expbak remap_schema=bds_rpt:report  dumpfile=expdp_bds_rpt_0718.dmp  logfile=expdp_bds_rpt_0718.log
参数：remap_schema=bds_rpt:report
   remap_schema= 原用户：新用户
注意：添加此参数后，oracle会自动创建新用户，并授予相应权限，如果该用户已存在，则会报错：
ORA-31684: Object type USER:"REPORT" already exists，但是此错误不影响数据的导入
 

本文出自 “狂奔的蜗牛” 博客，请务必保留此出处http://ccchencheng.blog.51cto.com/2419062/934551