Oracle数据泵使用

1、EXPDP：1、创建DIRECTORYcreate directory dir_dp as 'D:oracledir_dp' 2、使用目录：DATA_PUMP_DIRSQL> select * from dba_directories;OWNER DIRECTORY_NAME- -DIRECTORY_PATH-SYS ADMIN_DIRC:ADEaime_10.2_nt_pushoracle/md/adminSYS DATA_PUMP_DIRE:adminorcldpdumpSYS WORK_DIRC:ADEaime_10.2_nt_pushoracle/workSQL&

2、gt; grant read, write on directory data_pump_dir to scott;授权成功。3、EXPDP用法举例： 1)按用户导expdp scott/tigerorcl schemas=scott dumpfile=expdp.dmp DIRECTORY=dpdata1;2)并行进程parallelexpdp scott/tigerorcl directory=dpdata1 dumpfile=scott3.dmp parallel=40 job_name=scott33)按表名导expdp scott/tigerorcl TABLES=emp,dept

3、dumpfile=expdp.dmp DIRECTORY=dpdata1;4)按查询条件导expdp scott/tigerorcl 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=ful

4、l.dmp FULL=y;4、使用exclude,include导出数据 1、Include导出用户中指定类型的指定对象-仅导出lttfm用户下以B开头的所有表,包含与表相关的索引，备注等。不包含过程等其它对象类型：expdp lttfm/lttfmfgisdb dumpfile=include_1.dmp logfile=include_1.log directory=dir_dp job_name=my_job include=TABLE:"LIKE 'B%'"-导出lttfm用户下排除B$开头的所有表：expdp lttfm/lttfmfgisdb s

5、chemas=lttfm dumpfile=include_1.dmp logfile=include_1.log directory=dir_dp job_name=my_job include=TABLE:"NOT LIKE 'B$%'"-仅导出lttfm用户下的所有存储过程：expdp lttfm/lttfmfgisdb schemas=lttfm dumpfile=include_1.dmp logfile=include_1.log directory=dir_dp job_name=my_job include=PROCEDURE; 2、Excl

6、ude导出用户中指定类型的指定对象-导出lttfm用户下除TABLE类型以外的所有对象，如果表不导出那么与表相关的索引，约束等与表有关联的对象类型也不会被导出:expdp lttfm/lttfmfgisdb schemas=lttfm dumpfile=exclude_1.dmp logfile=exclude_1.log directory=dir_dp job_name=my_job exclude=TABLE;-导出lttfm用户下排除B$开头的所有表：expdp lttfm/lttfmfgisdb dumpfile=include_1.dmp logfile=include_1.log

7、 directory=dir_dp job_name=my_job exclude=TABLE:"LIKE'b$%'"-导出lttfm用户下的所有对象，但是对于表类型只导出以b$开头的表：expdp lttfm/lttfmfgisdb dumpfile=include_1.dmp logfile=include_1.log directory=dir_dp job_name=my_job exclude=TABLE:"NOT LIKE 'b$%'"3、备注： 1、directory=dir_dp必须放在前面，如果将其放置最

8、后，会提示 ORA-39002: 操作无效 ORA-39070: 无法打开日志文件。ORA-39087: 目录名 DATA_PUMP_DIR; 无效 2、在导出过程中，DATA DUMP 创建并使用了一个名为SYS_EXPORT_SCHEMA_01的对象，此对象就是DATA DUMP导出过程中所用的JOB名字，如果在执行这个命令时如果没有指定导出的JOB名字那么就会产生一个默认的JOB名字，如果在导出过程中指定JOB名字就为以指定名字出现 如下改成： expdp lttfm/lttfmfgisdb schemas=lttfm directory=dir_dp dumpfile =expdp_t

9、est1.dmp logfile=expdp_test1.log,job_name=my_job1; 3、导出语句后面不要有分号，否则如上的导出语句中的job表名为my_job1;，而不是my_job1。因此导致expdp lttfm/lttfm attach=lttfm.my_job1执行该命令时一直提示找不到job表 4、创建的目录一定要在数据库所在的机器上。否则也是提示: IMPDP：1、导到指定用户下impdp scott/tiger DIRECTORY=dpdata1 DUMPFILE=expdp.dmp SCHEMAS=scott;2、改变表的ownerimpdp system/m

10、anager DIRECTORY=dpdata1 DUMPFILE=expdp.dmp TABLES=scott.dept REMAP_SCHEMA=scott:system;3)导入表空间impdp system/manager DIRECTORY=dpdata1 DUMPFILE=tablespace.dmp TABLESPACES=example;4)导入数据库impdb system/manager DIRECTORY=dump_dir DUMPFILE=full.dmp FULL=y;5)追加数据impdp system/manager DIRECTORY=dpdata1 DUMPF

11、ILE=expdp.dmp SCHEMAS=system TABLE_EXISTS_ACTION 3、案例：将数据库A中HR用户下所有对象导入到SCOTT用户下：1）导出HR方案： expdp system/adminorcl schemas=hr dumpfile=hr.dmp DIRECTORY=data_pump_dir nologfle=y;2）将hr.dmp导入SCOTT方案：3）效果就是HR下面的对象都复制到了SCOTT用户下： 4、IMPDP命令行选项与EXPDP有很多相同的,不同的有:1、REMAP_DATAFILE该选项用于将源数据文件名转变为目标数据文件名,在不同平台之间搬

12、移表空间时可能需要该选项.REMAP_DATAFIEL=source_datafie:target_datafile2、REMAP_SCHEMA该选项用于将源方案的所有对象装载到目标方案中.REMAP_SCHEMA=source_schema:target_schema3、REMAP_TABLESPACE将源表空间的所有对象导入到目标表空间中REMAP_TABLESPACE=source_tablespace:targettablespace如果是多个表空间，则用逗号分开，如REMAP_TABLESPACE=source_tablespace1:targettablespace1,source

13、_tablespace2:targettablespace24、REUSE_DATAFILES该选项指定建立表空间时是否覆盖已存在的数据文件.默认为NREUSE_DATAFIELS=Y | N5、SKIP_UNUSABLE_INDEXES指定导入是是否跳过不可使用的索引,默认为N6、SQLFILE指定将导入要指定的索引DDL操作写入到SQL脚本中SQLFILE=directory_object:file_nameImpdp scott/tiger DIRECTORY=dump DUMPFILE=tab.dmp SQLFILE=a.sql7、STREAMS_CONFIGURATION指定是否导入

14、流元数据(Stream Matadata),默认值为Y.8、TABLE_EXISTS_ACTION该选项用于指定当表已经存在时导入作业要执行的操作,默认为SKIPTABBLE_EXISTS_ACTION=SKIP | APPEND | TRUNCATE | FRPLACE 当设置该选项为SKIP时,导入作业会跳过已存在表处理下一个对象;当设置为APPEND时,会追加数据,为TRUNCATE时,导入作业会截断表,然后为其追加新数据;当设置为REPLACE时,导入作业会删除已存在表,重建表病追加数据,注意,TRUNCATE选项不适用与簇表和NETWORK_LINK选项9、TRANSFORM该选项用

15、于指定是否修改建立对象的DDL语句TRANSFORM=transform_name:value:object_typeTransform_name用于指定转换名,其中SEGMENT_ATTRIBUTES用于标识段属性(物理属性,存储属性,表空间,日志等信息),STORAGE用于标识段存储属性,VALUE用于指定是否包含段属性或段存储属性,object_type用于指定对象类型.Impdp scott/tiger directory=dump dumpfile=tab.dmp Transform=segment_attributes:n:table10、TRANSPORT_DATAFILES该选

16、项用于指定搬移空间时要被导入到目标数据库的数据文件TRANSPORT_DATAFILE=datafile_nameDatafile_name用于指定被复制到目标数据库的数据文件Impdp system/manager DIRECTORY=dump DUMPFILE=tts.dmpTRANSPORT_DATAFILES=/user01/data/tbs1.f调用IMPDPEXPDP命令行选项1. ATTACH该选项用于在客户会话与已存在导出作用之间建立关联.语法如下ATTACH=schema_name.job_nameSchema_name用于指定方案名,job_name用于指定导出作业名.注意

17、,如果使用ATTACH选项,在命令行除了连接字符串和ATTACH选项外,不能指定任何其他选项,示例如下:Expdp scott/tiger ATTACH=scott.export_job2. CONTENT该选项用于指定要导出的内容.默认值为ALLCONTENT=ALL | DATA_ONLY | METADATA_ONLY当设置CONTENT为ALL时,将导出对象定义及其所有数据.为DATA_ONLY时,只导出对象数据,为METADATA_ONLY时,只导出对象定义Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dump CONTENT=METADAT

18、A_ONLY3. DIRECTORY指定转储文件和日志文件所在的目录DIRECTORY=directory_objectDirectory_object用于指定目录对象名称.需要注意,目录对象是使用CREATE DIRECTORY语句建立的对象,而不是OS目录Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dump4. DUMPFILE用于指定转储文件的名称,默认名称为expdat.dmpDUMPFILE=directory_object:file_name ,.Directory_object用于指定目录对象名,file_name用于指定转储文件名.需

19、要注意,如果不指定directory_object,导出工具会自动使用DIRECTORY选项指定的目录对象Expdp scott/tiger DIRECTORY=dump1 DUMPFILE=dump2:a.dmp5. ESTIMATE指定估算被导出表所占用磁盘空间分方法.默认值是BLOCKSESTIMATE=BLOCKS | STATISTICS设置为BLOCKS时,oracle会按照目标对象所占用的数据块个数乘以数据块尺寸估算对象占用的空间,设置为STATISTICS时,根据最近统计值估算对象占用空间Expdp scott/tiger TABLES=emp ESTIMATE=STATIST

20、ICS DIRECTORY=dump DUMPFILE=a.dump6. ESTIMATE_ONLY指定是否只估算导出作业所占用的磁盘空间,默认值为NEXTIMATE_ONLY=Y | N设置为Y时,导出作用只估算对象所占用的磁盘空间,而不会执行导出作业,为N时,不仅估算对象所占用的磁盘空间,还会执行导出操作.Expdp scott/tiger ESTIMATE_ONLY=y NOLOGFILE=y7. EXCLUDE(具体见2、Exclude导出用户中指定类型的指定对象)该选项用于指定执行操作时释放要排除对象类型或相关对象EXCLUDE=object_type:name_clause ,.O

21、bject_type用于指定要排除的对象类型,name_clause用于指定要排除的具体对象.EXCLUDE和INCLUDE不能同时使用Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dup EXCLUDE=VIEW8. FILESIZE指定导出文件的最大尺寸,默认为0,(表示文件尺寸没有限制)9. FLASHBACK_SCN指定导出特定SCN时刻的表数据FLASHBACK_SCN=scn_valueScn_value用于标识SCN值.FLASHBACK_SCN和FLASHBACK_TIME不能同时使用Expdp scott/tiger DIRECTOR

22、Y=dump DUMPFILE=a.dmp FLASHBACK_SCN=35852310. FLASHBACK_TIME指定导出特定时间点的表数据FLASHBACK_TIME=”TO_TIMESTAMP(time_value)”Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp FLASHBACK_TIME=“TO_TIMESTAMP(25-08-2004 14:35:00,DD-MM-YYYY HH24:MI:SS)”11. FULL指定数据库模式导出,默认为NFULL=Y | N为Y时,标识执行数据库导出.12. HELP指定是否显示EXPDP命

23、令行选项的帮助信息,默认为N当设置为Y时,会显示导出选项的帮助信息.Expdp help=y13. INCLUDE（具体见1、Include导出用户中指定类型的指定对象）指定导出时要包含的对象类型及相关对象INCLUDE = object_type:name_clause , 14. JOB_NAME指定要导出作用的名称,默认为SYS_XXXJOB_NAME=jobname_stringSELECT * FROM DBA_DATAPUMP_JOBS;-查看存在的job15. LOGFILE指定导出日志文件文件的名称,默认名称为export.logLOGFILE=directory_object

24、:file_nameDirectory_object用于指定目录对象名称,file_name用于指定导出日志文件名.如果不指定directory_object.导出作用会自动使用DIRECTORY的相应选项值.Expdp scott/tiger DIRECTORY=dump DUMPFILE=a.dmp logfile=a.log16. NETWORK_LINK指定数据库链名,如果要将远程数据库对象导出到本地例程的转储文件中,必须设置该选项.如：expdp gwm/gwm directory=dir_dp NETWORK_LINK=igisdb tables=p_street_area dum

25、pfile =p_street_area.dmp logfile=p_street_area.log job_name=my_jobigisdb是目的数据库与源数据的链接名，dir_dp是目的数据库上的目录而如果直接用使用连接字符串(fgisdb),expdp属于服务端工具，expdp生成的文件默认是存放在服务端的17. NOLOGFILE该选项用于指定禁止生成导出日志文件,默认值为N.18. PARALLEL指定执行导出操作的并行进程个数,默认值为1注：并行度设置不应该超过CPU数的2倍，如果cpu为2个，可将PARALLEL设为2，在导入时速度比PARALLEL为1要快 而对于导出的文件，

26、如果PARALLEL设为2，导出文件只有一个，导出速度提高不多，因为导出都是到同一个文件，会争抢资源。所以可以设置导出文件为两个,如下所示： expdp gwm/gwm directory=d_test dumpfile=gwmfile1.dp,gwmfile2.dp parallel=219. PARFILE指定导出参数文件的名称PARFILE=directory_path file_name20. QUERY用于指定过滤导出数据的where条件QUERY=schema. table_name: query_clauseSchema用于指定方案名,table_name用于指定表名,query

27、_clause用于指定条件限制子句.QUERY选项不能与CONNECT=METADATA_ONLY,EXTIMATE_ONLY,TRANSPORT_TABLESPACES等选项同时使用.Expdp scott/tiger directory=dump dumpfile=a.dmp Tables=emp query=WHERE deptno=2021. SCHEMAS该方案用于指定执行方案模式导出,默认为当前用户方案.22. STATUS指定显示导出作用进程的详细状态,默认值为023. TABLES指定表模式导出TABLES=schema_name.table_name:partition_na

28、me,Schema_name用于指定方案名,table_name用于指定导出的表名,partition_name用于指定要导出的分区名.24. TABLESPACES指定要导出表空间列表25. TRANSPORT_FULL_CHECK该选项用于指定被搬移表空间和未搬移表空间关联关系的检查方式,默认为N.当设置为Y时,导出作用会检查表空间直接的完整关联关系,如果表空间所在表空间或其索引所在的表空间只有一个表空间被搬移,将显示错误信息.当设置为N时,导出作用只检查单端依赖,如果搬移索引所在表空间,但未搬移表所在表空间,将显示出错信息,如果搬移表所在表空间,未搬移索引所在表空间,则不会显示错误信息.

29、26. TRANSPORT_TABLESPACES指定执行表空间模式导出27. VERSION指定被导出对象的数据库版本,默认值为COMPATIBLE.VERSION=COMPATIBLE | LATEST | version_string为COMPATIBLE时,会根据初始化参数COMPATIBLE生成对象元数据;为LATEST时,会根据数据库的实际版本生成对象元数据.version_string用于指定数据库版本字符串.调用EXPDPexp与impexp的关键字说明：关键字   说明 (默认值)       &

30、#160;-USERID   用户名/口令            BUFFER   数据缓冲区大小         FILE     输出文件 (EXPDAT.DMP)  COMPRESS  导入到一个区 (Y)      GRANTS  

31、  导出权限 (Y)          INDEXES   导出索引 (Y)          DIRECT    直接路径 (N)  -直接导出速度较快        LOG      屏幕输出的日志文件  

32、    ROWS      导出数据行 (Y)        CONSISTENT 交叉表的一致性 (N)   FULL        导出整个文件 (N)OWNER      所有者用户名列表TABLES     表名列表RECORDLENGTH  

33、; IO记录的长度INCTYPE     增量导出类型RECORD       跟踪增量导出 (Y)TRIGGERS     导出触发器 (Y)STATISTICS    分析对象 (ESTIMATE)PARFILE      参数文件名CONSTRAINTS  导出的约束条件 (Y)OBJECT_CONSISTENT    只在对象导出

34、期间设置为只读的事务处理 (N)FEEDBACK             每 x 行显示进度 (0)FILESIZE             每个转储文件的最大大小FLASHBACK_SCN        用于将会话快照设置回以前状态的 SCNFLASHBACK_TIME 

35、60;     用于获取最接近指定时间的 SCN 的时间QUERY                用于导出表的子集的 select 子句RESUMABLE            遇到与空格相关的错误时挂起 (N)RESUMABLE_NAME     

36、  用于标识可恢复语句的文本字符串RESUMABLE_TIMEOUT    RESUMABLE 的等待时间TTS_FULL_CHECK       对 TTS 执行完整或部分相关性检查TABLESPACES          要导出的表空间列表TRANSPORT_TABLESPACE 导出可传输的表空间元数据 (N)TEMPLATE      &#

37、160;      调用 iAS 模式导出的模板名常用的exp关键字 1、full用于导出整个数据库，在rows=n一起使用，导出整个数据库的结构。   如：exp userid=gwm/gwm file=/test.dmp log=test.log full=y rows=n direct=y2、OWNER和TABLES，用于定义exp导出的对象，可加上query条件设置导出的行数   如：exp userid=gwm/gwm file=/test.dmp log=test.log owner=gwm

38、table=(table1,table2)  query="'where rownum<11'"3、buffer和feedback 若导出数据较大，考虑使用这两个参数。   如：exp userid=gwm/gwm file=/test.dmp log=test.log feedback=10000 buffer=100000000 tables=(table1,table2)4、file和log 用于指定备份的dmp名称和log名称5、compress 不压缩导出数据的内容，默认y6、filesize 若导出的数据文件大

39、，应该用该参数，限制文件大小不要超过2g   如：exp userid=gwm/gwm file=/test1,test2,test3,test4,test5 filesize=2G log=test.log        这样将创建test1.dmp,test2.dmp等，每个文件大小为2g。  imp关键字说明关键字   说明 (默认值)        关键字   

40、0;  说明 (默认值)-USERID   用户名/口令           FULL       导入整个文件 (N)BUFFER   数据缓冲区大小        FROMUSER    所有者用户名列表FILE     输入文件 (EXPDAT

41、.DMP)  TOUSER     用户名列表SHOW     只列出文件内容 (N)     TABLES      表名列表IGNORE   忽略创建错误 (N)    RECORDLENGTH  IO 记录的长度GRANTS   导入权限 (Y)       &#

42、160;  INCTYPE     增量导入类型INDEXES   导入索引 (Y)         COMMIT       提交数组插入 (N)ROWS     导入数据行 (Y)        PARFILE      参数

43、文件名LOG     屏幕输出的日志文件    CONSTRAINTS    导入限制 (Y)DESTROY                覆盖表空间数据文件 (N)INDEXFILE              将表/索引信息写入指定的文件SKIP_UNUSABLE_INDEXES  跳过不可用索引的维护 (N)FEEDBACK               每 x 行显示进度 (0)TOID_NOVALIDATE