- 管理员用户下:system建立目录对象
create directory MY_DIR 'c:\dir1'
--授权给scott用户读写
grant read,write on directory MY_DIR to scott;
- 导出(入)scott用户的student address表
expdp scott/scott@orcl directory=MY_DIR dumpfile=expdp_scott1.dmp
tables=(student,address)
--导入student,address
impdb scott/scott@orcl directory=MY_DIR dumpfile=expdp_scott1.dmp
- 导出scott的student address表的表结构,不导出数据 reuse_dumpfiles=y(覆盖之前的 N不覆盖)
expdp scott/scott@orcl directory=MY_DIR dumpfile=expdp_scott1.dmp
tables=(student,address) content=metadata_only reuse_dumpfiles=y
- 导出scott的student address表的数据,不导出结构
expdp scott/scott@orcl directory=MY_DIR dumpfile=expdp_scott1.dmp
tables=(student,address) content=data_only reuse-dumpfiles=y
- 导出scott、hr用户的内容
expdp system/system@orcl directory=MY_DIR dumpfile=expdp_scott1.dmp
schemas=(scott,hr) job_name=expdp1
- 导出student表和其上的约束索引
--传统导出
exp scott/scott@orcl tables=student file=c:\exp11.dmp
--然后导入
imp scott/scott@orcl file=c:\exp11.dmp
expdp scott/scott@orcl directory=MY_DIR dumpfile=expdp_scott3.dmp tables=student
- 导出student表的约束,不导出索引
--传统的导出
exp scott/scott@orcl tables=student file=c:\exp11.dmp indexes=n
expdp scott/scott@orcl directory=MY_DIR dumpfile=expdp_scott4.dmp tables=student
exclude=index --排除索引
- 导出student表,不导出索引,不导出约束
--传统
exp scott/scott@orcl tables=student file=c:\exp11.dmp indexes=n constraints=n
expdp scott/scott@orcl directory=MY_DIR dumpfile=expdp_scott4.dmp tables=student
exclude=index,constraint --排除索引约束
- 对于scott对象,不导出student address表,但导出其他表
expdp scott/scott@orcl tables=student file=c:\exp11.dmp
exclude=table:"in('STUDENT')" exclude=table:"in('ADDRESS')"
- 导出student表约束,索引;导出address表的结构和数据,不导出约束索引
expdp scott/scott@orcl directory=MY_DIR dumpfile=expdp6.dmp tables=(student,address)
exclude=constraint:"in('CON_ZZ')" exclude=index:"in('IND_ADD_SNO')"
- 导出student表约束,索引;导出address表的结构和数据,不导出约束索引(参数文件)
expdp scott/scott@orcl parfile='c:\exp1.txt'
exp1.txt文件内容:
directory=MY_DIR dumpfile=expdp7.dmp
include=table:"in('STUDENT','ADDRESS')"
include=index:"in('PK_SNO','IND_STU_SNAME')"--包含
include=constraint:"in('PK_SNO','CON_SAGE')"--包含
- 导出student表记录sno>1的记录,导出address表sno=2的记录
expdp scott/scott@orcl parfile='c:\exp2.txt'
exp2.txt文件内容:
directory=MY_DIR dumpfile=expdp8.dmp
reuse_dumpfiles=y--覆盖之前的,
tables=student,address
query=student:"where sno>1",address:"where sno=2"--query子集
- remap_schema的使用:用于将对象从一个用户下导入到另一个用户下
expdp scott/scott@orcl directory=MY_DIR dumpfile=expdp1.dmp schemas=scott
grant read,write on directory MY_DIR on hr
hr进行导入
impdp hr/hr@orcl directory=MY_DIR dumpfile=expdp1.dmp tables=student
remap_schema=scott:hr
- remap_tablespace:用于将对象从一个表空间下导入到另外一个表空间下
expdp scott/scott@orcl directory=MY_DIR dumpfile=expdp1.dmp schemas=scott
impdp hr/hr@orcl directory=MY_DIR dumpfile=expdp1.dmp remap_tablespace=users:example