查詢當前oracle中创建的目录
select * from dba_directories;
1，创建datapump用目录
SQL> conn / as sysdba
SQL> create or replace directory mydump_dir as '/data2/exp';
用戶應該具有create_any_directories 權限
服務器參數utl_file_dir可以控制往操作系統上的寫能力,這種方法控制粒度小,比較危險.
刪除dir
drop directory mydump_dir;
2，赋予相应的权限
SQL> grant read, write on directory mydump_dir to scott;
SQL> grant resource to scott;
先说下，有两个属性dumpfile和logfile，都可以不指定，不指定的话
dumpfile默认为exp.dmp
logfile默认为exp.log
导出自己的表,directory指定路径 tables指定要导出的表
expdp scott/tiger directory=mydump_dir tables=emp
导出指定用户的表
expdp scott/tiger directory=mydump_dir tables=test.tab1
只导出表中的数据
expdp scott/tiger directory=mydump_dir tables=emp content=data_only content=data_only
只导出表定义 ※：没有content属性的话等同于content=all，也就是整张表全部导出
expdp scott/tiger directory=mydump_dir tables=emp content=metadata_only content=metadata_only
导入表格
impdp scott/tiger directory=mydump_dir dumpfile=exp.dmp tables=emp
只导入表中的数据
impdp scott/tiger directory=mydump_dir dumpfile=exp.dmp tables=emp content=data_only ;
只导入表定义
impdp scott/tiger directory=mydump_dir dumpfile=exp.dmp tables=emp content=metadata_only content=metadata_only
SCHEMAS mode （会导出指定schemas，即用户的所有对象）
导出scott模式下所有对象
expdp scott/tiger directory=mydump_dir schemas=scott
或者是省略schemas，这样会默认导出执行scott用户的所有对象
expdp scott/tiger directory=mydump_dir
导入
impdp scott/tiger directory=mydump_dir dumpfile=exp.dmp schemas=scott
省略schemas，效果是一样的
impdp scott/tiger directory=mydump_dir dumpfile=exp.dmp
导入到不同的用户,scott导入hr用户
impdp scott/tiger directory=mydump_dir dumpfile=exp.dmp remap_schema=scott:hr TRANSFORM=segment_attributes:n
TABLESPACES mode
导出,tablespaces指定要导出的tablespace
expdp system/manager directory=mydump_dir tablespaces=users
导入
impdp system/Foxconn99 directory=mydump_dir dumpfile=exp.dmp tablespaces=users
full database mode
导出整個数据库
expdp system/Foxconn99 directory=mydump_dir full=y
导入整个数据库
impdp system/Foxconn99 directory=mydump_dir dumpfile=exp.dmp full=y
最后一个可以预计生成的dat文件的大小 关键字：estimate_only=y
expdp scott/tiger directory=mydump_dir estimate_only=y
导出的结构生成SQLFILE
expdp system/Foxconn99 directory=mydump_dir tables=SCOTT.EMP DUMPFILE=expdat.dmp content=metadata_only content=metadata_only
impdp system/Foxconn99 directory=mydump_dir DUMPFILE=expdat.dmp SQLFILE=emp.sql
不导出统计
expdp system/Foxconn99 directory=mydump_dir tables=SCOTT.EMP DUMPFILE=expdat.dmp exclude=STATISTICS
导入忽略存储选项
impdp system/Foxconn99 directory=mydump_dir DUMPFILE=expdat.dmp TRANSFORM=segment_attributes:n
并行导出和导入,大小设置,压缩选项
Note:(compression=ALL只有11g才有，10g只能用metadata_only 或者none)
expdp system/Foxconn99 exclude=STATISTICS full=y compression=metadata_only parallel=10 filesize=512M directory=mydump_dir dumpfile=expdat.dmp logfile=expdp1.log
imp logfile=expdp1.logpdp system/Foxconn99 full=y parallel=10 directory=mydump_dir dumpfile=expdat.dmp
PARFILE用法
FULL=Y
EXCLUDE=SCHEMA:"IN ('SYSMAN', 'OLAPSYS','SYSTEM','OLAPSYS','IX','TSMSYS')"
EXCLUDE=TABLE:"IN ('NAME', 'ADDRESS')"
expdp system/Foxconn99 directory=mydump_dir tables=SCOTT.EMP DUMPFILE=expdat.dmp parfile=exp.par
REMAP TABLE 和REMAP SCHEMA
impdp xx/xx direcore=xx dumpfile=xx tables=owner.tname remap_table=tname:tname_new
impdp xx/xx direcore=xx dumpfile=xx REMAP_SCHEMA=source_schema:target_schema
導出表到不同用戶不同表
expdp sys/foxconncard2010 parfile=dp1.par
DIRECTORY = DMP_DIR
exclude = STATISTICS
DUMPFILE = ECARD_ZZ.ECARD_RAWXFDATA_VENDING.dmp
LOGFILE = ECARD_ZZ.ECARD_RAWXFDATA_VENDING.log
TABLES = ECARD_ZZ.ECARD_RAWXFDATA_VENDING
impdp sys/foxconncard2010 parfile=dp1.par
DIRECTORY = mydump_dir
DUMPFILE = ECARD_ZZ.ECARD_RAWXFDATA_VENDING.dmp
LOGFILE = ECARD_ZZ.ECARD_RAWXFDATA_VENDING.log
TABLES = ECARD_ZZ.ECARD_RAWXFDATA_VENDING
REMAP_SCHEMA = ECARD_ZZ:DATABACKUP
REMAP_TABLE = ECARD_RAWXFDATA_VENDING:ecard_raw_x_v_zz20161107
TRANSFORM = segment_attributes:n
