Oracle的导入导出命令

1、Oracle的导入导出命令 ORACLE导入导出命令解析 本文对Oracle数据的导入导出 imp ,exp 两个命令进行了介绍, 并对其相应的参数进行了说明,然后通过一些示例进行演练,加深理解.文章最后对运用这两个命令可能出现的问题(如权限不够,不同oracle版本)进行了探讨,并提出了相应的解决方案;本文部分内容摘录自网络,感谢网友的经验总结;  一.说明   oracle 的exp/imp命令用于实现对数据库的导出/导入操作;   exp命令用于把数据从远程数据库服务器导出至本地,生成dmp文件;   imp命令用于把

2、本地的数据库dmp文件从本地导入到远程的Oracle数据库中。二.语法可以通过在命令行输入 imp help=y 获取imp的语法信息:=C:Documents and Settingsauduser>imp help=yImport: Release .1 - Production on 星期二 5月 20 18:21:57 2008(c) Copyright 2001 Oracle Corporation. All rights reserved.可以通过输入 IMP 命令和您的用户名/口令后接用户名/口令的命令:例程: IMP SCOTT/TIGER或者, 可以通过输

3、入 IMP 命令和各种参数来控制“导入”按照不同参数。要指定参数，您可以使用关键字:格式: IMP KEYWORD=value 或 KEYWORD=(value1,value2,.,vlaueN)例程: IMP SCOTT/TIGER IGNORE=Y TABLES=(EMP,DEPT) FULL=N或 TABLES=(T1: P1,T1: P2)，如果 T1 是分区表USERID 必须是命令行中的第一个参数。关键字 说明（默认）        关键字      说明（默认）

4、-USERID   用户名/口令           FULL         导入整个文件 (N)BUFFER   数据缓冲区大小         FROMUSER     所有人用户名列表FILE     输入文件

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

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

7、;     屏幕输出的日志文件   CONSTRAINTS 导入限制 (Y)DESTROY                覆盖表空间数据文件 (N)INDEXFILE              将表/索引信息写入指定的文件SKIP_UNUSABLE_INDEX

8、ES 跳过不可用索引的维护 (N)FEEDBACK               每 x 行显示进度 (0)TOID_NOVALIDATE        跳过指定类型 ID 的验证FILESIZE               每个转储文件的最大大小ST

9、ATISTICS             始终导入预计算的统计信息RESUMABLE              遇到与空格有关的错误时挂起 (N)RESUMABLE_NAME         用来标识可恢复语句的文本字符串RESUMABLE_TIMEOUT &

10、#160;    RESUMABLE 的等待时间COMPILE                编译过程, 程序包和函数 (Y)下列关键字仅用于可传输的表空间TRANSPORT_TABLESPACE 导入可传输的表空间元数据 (N)TABLESPACES 将要传输到数据库的表空间DATAFILES 将要传输到数据库的数据文件TTS_OWNERS 拥有可传输表空间集中数据的用户=同样可以通过输入 exp help=y 获

11、取exp的语法信息Microsoft Windows XP 版本 5.1.2600(C) 版权所有 1985-2001 Microsoft Corp.C:Documents and Settingsauduser>exp help=yExport: Release .1 - Production on 星期二 5月 20 18:26:34 2008(c) Copyright 2001 Oracle Corporation. All rights reserved. 通过输入 EXP 命令和用户名/口令，您可以后接用户名/口令的命令:例程: EXP SCOTT/TI

12、GER或者，您也可以通过输入跟有各种参数的 EXP 命令来控制“导出”按照不同参数。要指定参数，您可以使用关键字:格式: EXP KEYWORD=value 或 KEYWORD=(value1,value2,.,valueN)例程: EXP SCOTT/TIGER GRANTS=Y TABLES=(EMP,DEPT,MGR)或 TABLES=(T1: P1,T1: P2)，如果 T1 是分区表USERID 必须是命令行中的第一个参数。关键字 说明(默认)        关键字    &

13、#160; 说明(默认)-USERID   用户名/口令           FULL         导出整个文件 (N)BUFFER   数据缓冲区大小         OWNER     所有者用户名列表FILE   

14、60;   输出文件 (EXPDAT.DMP) TABLES       表名称列表COMPRESS   导入到一个区 (Y) RECORDLENGTH IO 记录的长度GRANTS 导出权限 (Y)           INCTYPE      增量导出类型INDEXES    导出索引 (Y)  

15、;       RECORD       跟踪增量导出 (Y)DIRECT     直接路径 (N)            TRIGGERS     导出触发器 (Y)LOG        屏幕输出的日志文件 STATI

16、STICS   分析对象 (ESTIMATE)ROWS    导出数据行 (Y)        PARFILE      参数文件名CONSISTENT 交叉表一致性    CONSTRAINTS 导出约束条件 (Y)FEEDBACK             每 x 行显示进度

17、(0)FILESIZE             每个转储文件的最大大小FLASHBACK_SCN        用于回调会话快照的 SCNFLASHBACK_TIME       用来获得最接近于指定时间的 SCN 的时间QUERY          

18、;      用来导出表的子集的选择子句RESUMABLE            遇到与空格有关的错误时挂起 (N)RESUMABLE_NAME       用来标识可恢复语句的文本字符串RESUMABLE_TIMEOUT    RESUMABLE 的等待时间TTS_FULL_CHECK     &

19、#160; 对 TTS 执行完全或部分相关性检查TABLESPACES          要导出的表空间列表TRANSPORT_TABLESPACE 导出可传输的表空间元数据 (N)TEMPLATE 调用 iAS 模式导出的模板名称三.使用示例3.1 数据导出：1 将数据库SampleDB完全导出,用户名system 密码manager 导出到E:SampleDB.dmp中   exp system/managerTestDB file=E:sampleDB.dmp full=y2 将

20、数据库中system用户与sys用户的表导出   exp system/managerTestDB file=E:sampleDB.dmp owner=(system,sys)3 将数据库中的表 TableA,TableB 导出    exp system/managerTestDB file=E:sampleDB.dmp tables=(TableA,TableB)4 将数据库中的表tableA中的字段filed1 值为 "王五" 的数据导出   exp system/managerTestDB fil

21、e=E:sampleDB.dmp tables=(tableA) query=' where filed1='王五'   如果想对dmp文件进行压缩,可以在上面命令后面 加上 compress=y 来实现。3.2 数据的导入1 将备份数据库文件中的数据导入指定的数据库SampleDB 中,如果 SampleDB 已存在该表,则不再导入;   imp system/managerTEST file=E:sampleDB.dmp full=y ignore=y2 将d:daochu.dmp中的表table1 导入imp system/

22、managerTEST file=E:sampleDB.dmp tables=(table1)3. 导入一个完整数据库imp system/manager file=bible_db log=dible_db full=y ignore=y4. 导入一个或一组指定用户所属的全部表、索引和其他对象imp system/manager file=seapark log=seapark fromuser=seapark impsystem/manager file=seapark log=seapark fromuser=(seapark,amy,amyc,harold)5. 将一个用户所属的数据导

23、入另一个用户imp system/manager file=tank log=tank fromuser=seapark touser=seapark_copyimp system/manager file=tank log=tank fromuser=(seapark,amy)touser=(seapark1, amy1)6. 导入一个表imp system/manager file=tank log=tank fromuser=seapark TABLES=(a,b)7. 从多个文件导入imp system/manager file=(paycheck_1,paycheck_2,paych

24、eck_3,paycheck_4)log=paycheck, filesize=1G full=y8. 使用参数文件imp system/manager parfile=bible_tables.parbible_tables.par参数文件：#Import the sample tables used for the Oracle8i Database Administrator'sBible. fromuser=seapark touser=seapark_copy file=seapark log=seapark_import参数文件示例见附录9. 增量导入imp system.

25、/manager inctype= RECTORE FULL=Y FILE=A不少情况下要先将表彻底删除，然后导入。 四.参数说明4.1、8i EXP常用选项1、FULL，这个用于导出整个数据库，在ROWS=N一起使用时，可以导出整个数据库的结构。例如：exp sys file=./db_str.dmp log=./db_str.log full=y rows=n compress=y direct=y2、BUFFER和FEEDBACK，在导出比较多的数据时，我会考虑设置这两个参数。例如：exp new file=yw97_2003.dmp log=yw97_2003_3.log f

26、eedback=10000 buffer=100000000 tables=WO4,OK_YT3、FILL和LOG，这两个参数分别指定备份的DMP名称和LOG名称，包括文件名和目录，例子见上面。需要说明的是，EXP可以直接备份到磁带中，即使用FILE=/dev/rmt0(磁带设备名)，但是一般我们都不这么做，原因有二：一、这样做的速度会慢很多，二、现在一般都是使用磁带库的，不建议直接对磁带进行操作。至于没有使用磁带库的朋友可以考虑和UNIX的TAR结合使用。如果你真想使用EXP直接到磁带，你可以参考Metalink文章“EXPORTING TO TAPE ON UNIX SYSTEMS”（文档

27、号：30428.1），该文中有详细解释。4、COMPRESS参数将在导出的同时合并碎块，尽量把数据压缩到initial的EXTENT里，默认是N，一般建议使用。DIRECT参数将告诉 EXP直接读取数据，而不像传统的EXP那样，使用SELECT来读取表中的数据，这样就减少了SQL语句处理过程。一般也建议使用。不过有些情况下 DIRECT参数是无法使用的。5、如何使用SYSDBA执行EXP/IMP？这是一个很现实的问题，有时候我们需要使用SYSDBA来执行EXP/IMP，如进行传输表空间的EXP/IMP，以及在9i下用SYS用户来执行EXP/IMP时，都需要使用SYSDBA才可。我们可以使用下面

28、方式连入EXP/IMP：exp "'sys/sys as sysdba'" file=1.dmp tables=gototop.t rows=n6、QUERY参数后面跟的是where条件，值得注意的是，整个where子句需要使用""括起来，where子句的写法和SELECT中相同，如果是UNIX平台所有"和'都需要使用u26469屏蔽它们的特殊含义：exp gototop/gototop file=1.dmp log=1.log tables=cyx.t query="where c1=20 and c2=go

29、totop"如果是windows平台，则使用下面的格式：      exp c/cncn file=c.dmp log=c.log tables=t query="""where id=1 and name='gototop'"""4.2、8i IMP常用选项1、FROMUSER和TOUSER，使用它们实现将数据从一个SCHEMA中导入到另外一个SCHEMA中。2、IGNORE、GRANTS和INDEXES，其中IGNORE参数将忽略表的存在，继续导入，这个对

30、于需要调整表的存储参数时很有用，我们可以先根据实际情况用合理的存储参数建好表，然后直接导入数据。而GRANTS和INDEXES则表示是否导入授权和索引，如果想使用新的存储参数重建索引，或者为了加快到入速度，我们可以考虑将INDEXES设为N，而GRANTS一般都是Y。另外一个EXP/IMP都有的参数是PARFILE，它是用来定义EXP/IMP的参数文件，也就是说，上面的参数都可以写在一个参数文件中，但我们一般很少使用。4.4、Oracle9i EXP功能描述Oracle9i EXP在原有的基础上新增了部分新的参数，按功能主要分为以下几个部分：1、OBJECT_CONSISTENT - 用于设置

31、EXP对象为只读以保持对象的一致性。默认是N。2、FLASHBACK_SCN和FLASHBACK_TIME - 用于支持FLASHBACK功能而新增。3、RESUMABLE、RESUMABLE_NAME和RESUMABLE_TIMEOUT - 用于支持RESUMABLE空间分配而新增。4、TTS_FULL_CHECK - 用于在传输表空间时使用依赖性检查。5、TEMPLATE - 用于支持iAS。6、TABLESPACES - 设置表空间导出模式。个人觉得对于一般用户而言，这个才是新增参数中最实用的一个，可以让用户在原来的FULL、OWNER、TABLES的基础上多了一种选择，使得EXP更加灵

32、活。五、不同版本的EXP/IMP问题？一般来说，从低版本导入到高版本问题不大，麻烦的是将高版本的数据导入到低版本中，在Oracle9i之前，不同版本Oracle之间的EXP/IMP可以通过下面的方法来解决：1、在高版本数据库上运行低版本的catexp.sql；2、使用低版本的EXP来导出高版本的数据；3、使用低版本的IMP将数据库导入到底版本数据库中；4、在高版本数据库上重新运行高版本的catexp.sql脚本。但在9i中，上面的方法并不能解决问题。如果直接使用低版本EXP/IMP会出现如下错误：EXP-00008: ORACLE error %lu encounteredORA-00904:

33、 invalid column name这已经是一个公布的BUG，需要等到Oracle10.0才能解决，BUG号为2261，你可以到METALINK上去查看有关此BUG的详细信息。BUG归BUG，我们的工作还是要做，在没有Oracle的支持之前，我们就自己解决。在Oracle9i中执行下面的SQL重建exu81rls视图即可。CREATE OR REPLACE view exu81rls(objown,objnam,policy,polown,polsch,polfun,stmts,chkopt,enabled,spolicy)AS select , , r.pname

34、, r.pfschma, r.ppname, r.pfname,decode(bitand(r.stmt_type,1), 0,'', 'SELECT,')| decode(bitand(r.stmt_type,2), 0,'', 'INSERT,')| decode(bitand(r.stmt_type,4), 0,'', 'UPDATE,')| decode(bitand(r.stmt_type,8), 0,'', 'DELETE,'),r.check_opt,

35、r.enable_flag,DECODE(BITAND(r.stmt_type, 16), 0, 0, 1)from user$ u, obj$ o, rls$ rwhere u.user# = o.owner#and r.obj# = o.obj#and (uid = 0 oruid = o.owner# orexists ( select * from session_roles where role='SELECT_CATALOG_ROLE')/grant select on sys.exu81rls to public;/六、其他问题本文只讨论了Oracle8i和9i中

36、的EXP/IMP的一些情况，对于之前的版本，在8.0.X 中，除了QUERY参数不能用外，其它差别不大。针对没有QUERY的情况，我们可以先在数据库中使用查询条件建立临时中间表，然后使用EXP导出这个中间表即可。至于Oracle7因为目前使用的人较少，gototop不打算在此做详细解释了，如果读者朋友有需求，你可以参考Metalink文档：“Overview of Export and Import in Oracle7”（文档号：61949.1）。关于EXP/IMP的详细参数信息你可以通过EXP/IMP HELP=Y来获得。另外关于传输表空间的更多信息可以参考下面的Metelink文档，本文

37、不再详述。NOTE:77523.1 Transportable Tablespaces - An Example to setup and use.NOTE:100698.1 Perform tablespace point-in-time recovery using Transportable Tablespace.在进行并行EXP/IMP的时候，如果IMP过程建索引的话不建议同时运行5个以上的IMP，如果你想加快速度，可以在IMP的时候不建索引，这样只要内存允许，可以多跑几个，然后是SQL脚本创建需要的索引。注意：操作者要有足够的权限，权限不够它会提示。数据库是否可以连上, 可以用tnsping TestDB 来获得数据库 TestDB 能否连上。附录一：给用户增加导入数据权限的操作第一, 启动sql*puls第二，以system/manager登