oracle expdp impdp的简要说明
oracle expdp impdp的简要说明
使用数据泵expdp,impdp可以在服务端完成数据的导入、导出,效率比较高
不用设置环境变量 NLS_LANG,默认会去数据库的字符集
create directory backup as '/backup';
grant read,write on directory backup to scott;
1 基于数据库的导出、导入
a 数据库全库导出
expdp system/root directory=backup dumpfile=full.dmp logfile=full.log full=y
b 整个库导入
impdp system/root directory=backup dumpfile=full.dmp logfile=full2.log full=y
#导入某一个用户的数据,schemas指定
impdp system/root directory=backup dumpfile=full.dmp logfile=full2.log schemas=scott
impdp system/root directory=backup dumpfile=full.dmp logfile=full2.log schemas=scott,gpecnew,hsj1
2 基于用户(schema)的导出、导入(以scott用户为例)
a 将scott用户的数据导出
grant read,write on directory backup to scott;
expdp scott/root directory=backup dumpfile=scott.dmp logfile=scott.log schemas=scott
或者
expdp system/root directory=backup dumpfile=scott.dmp logfile=scott.log schemas=scott
b 进行数据的导入,对于与imp来说更简单,不再需要创建用户,只是需要创建表空间而已
impdp system/root directory=backup dumpfile=scott.dmp logfile=scott2.log schemas=scott
或者
impdp system/root directory=backup dumpfile=scott.dmp logfile=scott2.log full=y;
或者
impdp system/root directory=backup dumpfile=scott.dmp logfile=scott2.log
3 基于表的导出、导入 一次只能导出一个方案的表数据
a 导出数据表 dept,emp
expdp system/root directory=backup dumpfile=tables.dmp logfile=tables.log TABLES=scott.dept,scott.emp
或者
expdp scott/root directory=backup dumpfile=tables.dmp logfile=tables.log TABLES=dept,emp
b 将数据文件导入进去
impdp scott/root directory=backup dumpfile=tables.dmp logfile=tables2.log (full=y);
4 基于用户导出表,排除以V开头,Z开头的所有表,排除 t_bd_person,t_bd_users
a 导出
expdp hsj/hsj directory=backup dumpfile=hsj.dmp logfile=hsj.log schemas=hsj
exclude=table:\" like \'V%\'\",table:\"like \'Z%\",table:\" in\(\'T_BD_PERSON',\'T_BD_USERS'\)\"
或者使用parfile参数文件 hsj.par
userid=hsj/hsj
directory=backup
dumpfile=hsj.dmp
logfile=hsj.log
schemas=hsj
exclude=table: " like 'V% ' ",table: "like 'Z% ",table: " in ( 'T_BD_PERSON', 'T_BD_USERS' ) "
expdp parfile=hsj.par
b 导入
impdp hsj/hsj directory=backup dumpfile=hsj.dmp logfile=hsj2.log
说明:对于include参数,暂时无法完成 以V开头或者以Z开头的表的条件的导出,条件之间只能and,不能or
对于expdp、impdp,我们需要制定directory参数,可以参考下面两个语句
create directory backup as '/backup';
grant read,write on directory backup to scott;