expd,impd常用命令

以用户名:dzl 密码:test 实例：orcl 导出文件夹d:\dump为例
1.创建导入文件的路径
create directory dmp_dir as 'd:\dump';
2.查看文件路径
select * from dba_directories;
3.赋予权限
grant read,write on directory dmp_dir to dzl;
4.常用导出命令
4.1按用户导
expdp dzl/test@orcl schemas=dzl dumpfile=20181104.dmp DIRECTORY=dmp_dir;
4.2并行进程parallel
expdp dzl/test@orcl directory=dmp_dir dumpfile=20181104.dmp parallel=40 job_name=dzl3
4.3按表名导
expdp dzl/test@orcl TABLES=t_test dumpfile=20181104.dmp DIRECTORY=dmp_dir;
4.4按查询条件导
expdp dzl/test@orcl directory=dmp_dir dumpfile=20181104.dmp Tables=t_test query='WHERE deptno=20';
4.5按表空间导
expdp dzl/test@orcl DIRECTORY=dmp_dir DUMPFILE=20181104.dmp TABLESPACES=temp,users;
4.6导整个数据库
expdp dzl/test@orcl DIRECTORY=dmp_dir DUMPFILE=20181104.dmp FULL=y;
5还原数据
5.1导到指定用户下
impdp dzl/test DIRECTORY=dmp_dir DUMPFILE=20181104.dmp SCHEMAS=dzl;
5.2改变表的owner
impdp dzl/test@orcl DIRECTORY=dmp_dir DUMPFILE=20181104.dmp TABLES=t_test REMAP_SCHEMA=dzl:system;
5.3导入表空间
impdp dzl/test@orcl DIRECTORY=dmp_dir DUMPFILE=20181104.dmp TABLESPACES=users;
5.4导入数据库
impdb dzl/test@orcl DIRECTORY=dump_dir DUMPFILE=20181104.dmp FULL=y;
5.5追加数据
impdp dzl/test@orcl DIRECTORY=dmp_dir DUMPFILE=20181104.dmp SCHEMAS=dzl TABLE_EXISTS_ACTION
5.6不同版本的导入
IMPDP dzl/test@orcl schemas=dzl directory=dmp_dir dumpfile=20181104.dmp logfile=20181104.log version=10.2.0.1.0

转载于:https://www.cnblogs.com/dayoff/p/9909415.html