data pump相关

expdp

1.创建导出数据文件存放目录并赋予相关权限

create directory dpump_dir1 as 'e:\dpump\';
grant write,read on directory dpump_dir1 to hxl;

2.赋予用户datapump_exp_full_database角色

grant datapump_exp_full_database to hxl;

3.导出某个表

expdp hxl/hxl@orcl directory=dpump_dir1 dumpfile=hxl.dmp tables=tb_hxl_id

 

impdp

1.授予权限datapump_imp_full_database

Grant datapump_imp_full_database To hxl;

2.导入表

一下语句在表tb_hxl_id不存在的情况下执行,否则报错误.

impdp hxl/hxl@orcl directory=dpump_dir1 dumpfile=hxl.dmp tables=tb_hxl_id

3.导入已经存在的表并加载数据(table_exists_action=append)

impdp hxl/hxl DIRECTORY=expdp_dir DUMPFILE=tb_hxl_user_mid_hash.dmp tables=tb_hxl_user_mid_hash table_exists_action=append

4.将创建对象的DDL写入文件(SQLFILE)

impdp hxl/hxl@orcl directory=dpump_dir1 dumpfile=tb_hxl_id.dmp tables=tb_hxl_id SQLFILE=aa.sql

5.具体指定导入某个表(INCLUDE)

impdp hxl/hxl@orcl directory=dpump_dir1 dumpfile=tb_hxl_id.dmp INCLUDE=TABLE:\"IN ('TB_HXL_ID')\"

6.导入过滤掉某个对象(EXCLUDE)

impdp hxl/hxl@orcl directory=dpump_dir1 dumpfile=tb_hxl_id.dmp EXCLUDE=TABLE:\"IN ('TB_HXL_ID')\"