impdp/expdp 总结

 

impdp/expdp

 

1、创建DIRECTORY

create directory dir_dp as '/tmp';   --建议将DIRECTORY 建在 /tmp 表下面，该目录肯定存在，而且更容易记住，理解；

 

2、授权
Grant read,write on directory dir_dp to illusion ;

--查看目录及权限
SELECT privilege, directory_name, DIRECTORY_PATH FROM user_tab_privs t, all_directories d

 WHERE t.table_name(+) = d.directory_name ORDER BY 2, 1;

 

3、执行导出

① 全库导出：

expdp illusion/illusion@illusioned schemas=illusion directory=dir_dp dumpfile =expdp_test.dmp logfile=expdp_test.log;

 

② expdp 命令拓展：exclude/include 可以在导出时过滤/包括 表导出；

 expdp illusion/illusion@illusioned  directory=dir_dp  dumpfile=expdp_test.dump  full=y  parallel=10  exclude=statistics logfile=expdp_test.log  cluster=N  exclude=TABLE:\"LIKE \'T_BASE%\'\", exclude=TABLE:\"LIKE \'%20%\'\"

 

③ expdp 指定表导出：

expdp illusion/illusion@illusioned  TABLES=table_a,table_b,table_c   directory=dir_dp  dumpfile=expdp_test.dmp logfile=expdp_test.log

 

4、执行导入

① 常用导入，表空间及用户相同的的情况下

 impdp illusion/illusion@illusioned directory=dir_dp dumpfile=expdp_test.dmp   table_exists_action=replace

注： table_exists_action 参数配置;

1)  skip：默认操作

2)  replace：先drop表，然后创建表，最后插入数据

3)  append：在原来数据的基础上增加数据

4)  truncate：先truncate，然后再插入数据

 

②导入其他数据库，表空间及用户不存在，需要指定表空间及用户；

impdp demo/demo  directory=DPDATA  dumpfile=expdp_test.dmp REMAP_SCHEMA=illusion:demo REMAP_TABLESPACE=DATATBS_illusioned :PER   table_exists_action=replace  

注：REMAP_SCHEMA            　　--用户转换 

　　REMAP_TABLESPACE　　　　--用户表空间转换

 

5、导入汞数据后，有时会出现存储过程报错告警，此时只要用 SQL DEVELOPER 重新执行相应的存储过程，重建存储过程；

 

 

2017/08/12 拓展 

 

1.使用expdp要先在数据库中创建directory,并给相应的用户read,write权限.

① 创建逻辑目录，该命令不会在操作系统创建真正的目录，最好以system等管理员创建。

create directory dpdata1 as '/home/test/dump';

注： 该目录为导出的本地服务器的目录，若本地服务器无此目录，虽然oracle 没提示报错，创建是失败的；

② 查看管理理员目录（同时查看操作系统是否存在，因为Oracle并不关心该目录是否存在，如果不存在，则出错）

select * from dba_directories;   ---（查询结果为创建目录，无权限列表）

--查看目录及权限 (查询结果为全部权限  WRITE or READ)

SELECT privilege, directory_name, DIRECTORY_PATH FROM user_tab_privs t, all_directories d

WHERE t.table_name(+) = d.directory_name ORDER BY 2, 1;

③ 给scott用户赋予在指定目录的操作权限，最好以system、DBA等管理员赋予。

grant read,write on directory dpdata to scott;

 

2.常用导出方法

用expdp导出数据

1)导出用户

expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp directory=dump_dir;

2)导出表

expdp scott/tiger@orcl tables=emp,dept dumpfile=expdp.dmp directory=dump_dir;

3)按查询条件导

expdp scott/tiger@orcl directory=dump_dir dumpfile=expdp.dmp tables=emp query='where deptno=20';

4)按表空间导

expdp system/manager@orcl directory=dump_dir dumpfile=tablespace.dmp  tablespaces=temp,example;

5)导整个数据库

expdp system/manager@orcl directory=dump_dir dumpfile=full.dmp full=y;

 

用impdp导入数据：

1)导入用户（从用户scott导入到用户scott）

impdp scott/tiger@orcl directory=dump_dir dumpfile=expdp.dmp schemas=scott;

2)导入表（从scott用户中把表dept和emp导入到system用户中）

impdp system/manager@orcl directory=dump_dir dumpfile=expdp.dmptables=scott.dept,scott.emp remap_schema=scott:system;

3)导入表空间

impdp system/manager@orcl directory=dump_dir dumpfile=tablespace.dmp tablespaces=example;

4)导入数据库

impdb system/manager@orcl directory=dump_dir dumpfile=full.dmp full=y;

5)追加数据

impdp system/manager@orcl directory=dump_dir dumpfile=expdp.dmp schemas=system table_exists_action

 

自定义导入导出案例：

例① 按表导入导出导入； 

$expdp scott/tiger     tables=t_trans_ccs_20170809  dumpfile=expdpccs.dmp DIRECTORY=data_dir logfile=expdpccs.log

A、$impdp scott/tiger   tables=t_trans_ccs_20170809  dumpfile=expdpccs.dmp DIRECTORY=data_dir logfile=expdpccs.log  

B、$impdp scott/tiger   REMAP_TABLE=t_trans_ccs_20170809:t_trans_ccs  directory=data_dir  dumpfile=expdpccs.dmp  logfile=expdp_imp.log

说明：

REMAP_TABLE:重定义导入表名；

 

例② 按用户导出导入；

$expdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=test  LOGFILE=export.log

A、$impdp scott/tiger DUMPFILE=scott.dmp DIRECTORY=dmpdir SCHEMAS=test  LOGFILE=export.log

B、$impdp scott/tiger directory=DPDATA  dumpfile=DPT_cxwg.dmp REMAP_SCHEMA=develop03:CXWG  REMAP_TABLESPACE=DATATBS_CATT03:PER  table_exists_action=replace  

C、$impdp scott/tiger dumpfile=emp1.dmp directory=test_dir logfile=emp1.log remap_schema=jzh:park remap_tablespace=users:example remap_table=emp1:emp2

说明：

REMAP_TABLESPACE=DATATBS_CATT03:PER --重定义表空间

REMAP_TABLE=t_trans_ccs_20170809:t_trans_ccs --重定义表

REMAP_SCHEMA=develop03:CXWG --重定义用户

TABLE_EXISTS_ACTION ：当使用IMPDP完成数据库导入时，如遇到表已存在时；

Oracle提供给我们如下四种处理方式：

  a.忽略（SKIP，默认行为）；

  b.在原有数据基础上继续增加（APPEND）；

  c.先DROP表，然后创建表，最后完成数据插入（REPLACE）；

  d.先TRUNCATE，再完成数据插入（TRUNCATE）。

 

例③ 多表导出

$expdp scott/tiger DUMPFILE=tmp_dump.dmp DIRECTORY=dmpdir TABLES=(tmp_test:p1,tmp_test:p2)  JOB_NAME=tmp_dump LOGFILE=tmp_dump.log

$impdp scott/tiger DUMPFILE=tmp_dump.dmp DIRECTORY=dmpdir TABLES=(tmp_test:p1,tmp_test:p2)  JOB_NAME=tmp_dump LOGFILE=tmp_dump.log

 

例④ 全库导出 FULL=Y 

$expdp scott/tiger DUMPFILE=full.dmp DIRECTORY=dmpdir FULL=Y JOB_NAME=full

$impdp scott/tiger DUMPFILE=full.dmp DIRECTORY=dmpdir FULL=Y  include=TABLE:\" in \(select table_name from temp_20160614 \) \" 

说明：

include=TABLE:\" in \(select table_name from temp_20160614 \) \"   ---利用include 查临时表返回表名导入，更加灵活化；

 

例⑤ QUERY 按查询条件导出；

$expdp scott/timer DUMPFILE=tmp_200703.dmp DIRECTORY=dmpdir TABLES=tmp QUERY=\”where to_char\(create_time,\’yyyy-mm-dd\’\)\<\'2007-04\'\"

说明：

QUERY=\”where to_char\(create_time,\’yyyy-mm-dd\’\)\<\'2007-04\'\"   --- 按条件导出(主要用于按表导出选择条件)

 

例⑥ 按条件过滤表导出；

$expdp scott/tiger  directory=dump_dir1 dumpfile=scott.dump  full=y parallel=10 exclude=statistics logfile=log.log cluster=N exclude=TABLE:\"LIKE \'T_BASE%\'\", exclude=TABLE:\"LIKE \'%20%\'\"

说明：

exclude=statistics  --- 排除统计 

parallel=10           --- 并行处理

cluster=N             --- 是否使用集群，默认是Y，这里需要N

exclude=TABLE:\"LIKE \'T_BASE%\'\", exclude=TABLE:\"LIKE \'%20%\'\"  ---- 全库导出时过滤表名，like 模糊匹配表；

exclude=TABLE:\"LIKE \'T_BASE%\'\ or like \'%20%\'\"

 

例⑦ 导出并压缩

expdp scott/tiger schemas=\(develop,develop01\)  DIRECTORY=data_dir CONTENT=all compression=ALL DUMPFILE=dir.dump  logfile=dir.log

说明：

compression=ALL    ---expdp 导出并压缩

 

例⑧  按表空间导出

expdp  \'/ as sysdba\'  directory=dump_dir1 dumpfile=DPT.dump full=y parallel=10 exclude=statistics logfile=log.log  cluster=N  tablespaces=UNDOTBS1

说明：

 tablespaces=UNDOTBS1  --- 按表空间导出

 \'/ as sysdba\'    --- expd导出可以直接用sysdba  DBA权限导出 

 

例⑨  expdp多文件导出

expdp scott/tiger  dumpfile=test%U.dump  directory=dpump_dir schemas=test content=data_only FILESIZE=100M

说明：

dumpfile=test%U.DMP  ---导出多个文件，必须%U

FILESIZE=100M            ---定义每个导出文件的大小