Oracle数据泵使用.docx

EXPDP：1、创建DIRECTORYcreate directory dir_dp as D:\oracle\dir_dp; 2、使用目录：DATA_PUMP_DIRSQL select * from dba_directories;OWNER DIRECTORY_NAME------------------------------ ------------------------------DIRECTORY_PATH----------------------------------------------------------------SYS ADMIN_DIRC:\ADE\aime_10.2_nt_push\oracle/md/adminSYS DATA_PUMP_DIRE:\admin\orcl\dpdump\SYS WORK_DIRC:\ADE\aime_10.2_nt_push\oracle/workSQL grant read, write on directory data_pump_dir to scott;授权成功。3、EXPDP用法举例： 1)按用户导expdp scott/tiger@orcl schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1;2)并行进程parallelexpdp scott/tiger@orcl directory=dpdata1 dumpfile=scott3.dmp parallel=40 job_name=scott33)按表名导expdp scott/tiger@orcl TABLES=emp,dept dumpfile=expdp.dmp DIRECTORY=dpdata1;4)按查询条件导expdp scott/tiger@orcl directory=dpdata1 dumpfile=expdp.dmp Tables=emp query=WHERE deptno=20;5)按表空间导expdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=temp,example;6)导整个数据库expdp system/manager DIRECTORY=dpdata1 DUMPFILE=full.dmp FULL=y;4、使用exclude,include导出数据 1、Include导出用户中指定类型的指定对象--仅导出lttfm用户下以B开头的所有表,包含与表相关的索引，备注等。不包含过程等其它对象类型：expdp lttfm/lttfm@fgisdb dumpfile=include_1.dmp logfile=include_1.log directory=dir_dp job_name=my_job include=TABLE:\LIKE \B%\\--导出lttfm用户下排除B$开头的所有表：expdp lttfm/lttfm@fgisdb schemas=lttfm dumpfile=include_1.dmp logfile=include_1.log directory=dir_dp job_name=my_job include=TABLE:\NOT LIKE \B$%\\--仅导出lttfm用户下的所有存储过程：expdp lttfm/lttfm@fgisdb schemas=lttfm dumpfile=include_1.dmp logfile=include_1.log directory=dir_dp job_name=my_job include=PROCEDURE;2、Exclude导出用户中指定类型的指定对象--导出lttfm用户下除TABLE类型以外的所有对象，如果表不导出那么与表相关的索引，约束等与表有关联的对象类型也不会被导出:expdp lttfm/lttfm@fgisdb schemas=lttfm dumpfile=exclude_1.dmp logfile=exclude_1.log directory=dir_dp job_name=my_job exclude=TABLE;--导出lttfm用户下排除B$开头的所有表：expdp lttfm/lttfm@fgisdb dumpfile=include_1.dmp logfile=include_1.log directory