oracle数据泵的使用
逻辑备份：EXP / IMP1.表模式：
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> 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> 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'> 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'>
create user u1> 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> 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> 数据库链接已创建。
--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:reportdumpfile=expdp_bds_rpt_0718.dmplogfile=expdp_bds_rpt_0718.log
参数：remap_schema=bds_rpt:report
remap_schema= 原用户：新用户
注意：添加此参数后，oracle会自动创建新用户，并授予相应权限，如果该用户已存在，则会报错：
ORA-31684: Object type USER:"REPORT" already exists，但是此错误不影响数据的导入
页:
[1]