Oracle数据库的数据迁移

1、dba 经常会需要在多个数据库之间迁移数据测试数据库中的数据发布到产品库oltp数据库中的数据加载到数据仓库中等等oracle 10g中存在很多的迁移数据的方式，我们这章一一介绍。导出导入1、不同的oracle数据库之间转移数据对象，即使这些数据库位于不同的硬件或软件平台。2、我们可以使用导出工具，将指定的数据库对象（比如表）所包含的数据导出到一个特殊格式的文件中。该文件是一个专用的二进制格式的转储文件，他只能被导入工具所读取。如果这些被导出的对象还存在其他的相关对象，比如要被导出的表上还存在索引等，则导出工具会自动将这些相关的对象也提取出来，并放入到导出文件中。3、导出工具导出的转储文件中，

2、包含对象的定义信息以及实际的数据行4、导入导出是一个客户端工具，生成的文件是客户端的平台格式。5、导入导出还可以作为备份的补充手段，对数据库进行逻辑备份，例如可以通过导出工具，将某些重要表的数据抽取出来，生成转储文件。使用导出工具，具有四种模式，也就是可以导出四种数据1、表模式：可以导出某个用户下指定的表，授权的用户可以导出其他用户下的表2、用户模式：导出某个指定用户下所有的对象，授权的用户可以导出其他用户下面的所有对象3、数据库模式：导出除了sys以外的、数据库里所有的对象，只有已授权的用户才能在该模式中执行导出4、可传输表空间模式：导出某个指定表空间中的所有对象时，可以使用该模式。通过使用

3、该模式，可以将一组表空间从一个数据库快速的转移到另一个数据库里。速度相对数据库的导入导出来说，要快得多。因为就两个步骤：复制数据文件表空间的结构信息倒入到目标数据库c:documents and settingsliexp -helpexport: release 10.2.0.1.0 - production on 星期六 3月 6 10:01:35 2010copyright (c) 1982, 2005, oracle. all rights reserved.通过输入 exp 命令和您的用户名/口令, 导出操作将提示您输入参数: 例如: exp scott/tiger或者, 您也可以通

4、过输入跟有各种参数的 exp 命令来控制导出的运行方式。要指定参数, 您可以使用关键字: 格式: exp keyword=value 或 keyword=(value1,value2,.,valuen) 例如: exp scott/tiger grants=y tables=(emp,dept,mgr) 或 tables=(t1:p1,t1:p2), 如果 t1 是分区表userid 必须是命令行中的第一个参数。关键字 说明 (默认值) 关键字 说明 (默认值)-userid 用户名/口令 full 导出整个文件 (n)buffer 数据缓冲区大小 owner 所有者用户名列表file 输出文

5、件 (expdat.dmp) tables 表名列表compress 导入到一个区 (y) recordlength io 记录的长度grants 导出权限 (y) inctype 增量导出类型indexes 导出索引 (y) record 跟踪增量导出 (y)direct 直接路径 (n) triggers 导出触发器 (y)log 屏幕输出的日志文件 statistics 分析对象 (estimate)rows 导出数据行 (y) parfile 参数文件名consistent 交叉表的一致性 (n) constraints 导出的约束条件 (y)object_consistent 只在对

6、象导出期间设置为只读的事务处理 (n)feedback 每 x 行显示进度 (0)filesize 每个转储文件的最大大小flashback_scn 用于将会话快照设置回以前状态的 scnflashback_time 用于获取最接近指定时间的 scn 的时间query 用于导出表的子集的 select 子句resumable 遇到与空格相关的错误时挂起 (n)resumable_name 用于标识可恢复语句的文本字符串resumable_timeout resumable 的等待时间tts_full_check 对 tts 执行完整或部分相关性检查tablespaces 要导出的表空间列表tr

7、ansport_tablespace 导出可传输的表空间元数据 (n)template 调用 ias 模式导出的模板名成功终止导出, 没有出现警告。c:documents and settingsliexp a/a file=e:oracleproduct10.2.0oradataora10gdatafilea.dmp tables=a.a log=e:oracleproduct10.2.0oradataora10gdatafileorder.logexport: release 10.2.0.1.0 - production on 星期六 3月 6 10:12:08 2010copyrigh

8、t (c) 1982, 2005, oracle. all rights reserved.连接到: oracle database 10g enterprise edition release 10.2.0.1.0 - productionwith the partitioning, olap and data mining options已导出 zhs16gbk 字符集和 al16utf16 nchar 字符集即将导出指定的表通过常规路径. . 正在导出表 a导出了 49745 行成功终止导出, 没有出现警告。导出用户下指定的表（表模式），以a用户进行导出。c:documents and

9、settingsliexp a/a file=e:oracleproduct10.2.0oradataora10gdatafilea.dmp tables=sys.dba_objectsexport: release 10.2.0.1.0 - production on 星期六 3月 6 10:17:03 2010copyright (c) 1982, 2005, oracle. all rights reserved.连接到: oracle database 10g enterprise edition release 10.2.0.1.0 - productionwith the part

10、itioning, olap and data mining options已导出 zhs16gbk 字符集和 al16utf16 nchar 字符集即将导出指定的表通过常规路径.exp-00009: 没有导出 sys 的表 dba_objects 的权限导出成功终止, 但出现警告。导出另外一个用户下面的对象时，权限不够。c:documents and settingsliexp a/a file=e:oracleproduct10.2.0oradataora10gdatafilea.dmp owner=a log=e:oracleproduct10.2.0oradataora10gdataf

11、ileorder.logexport: release 10.2.0.1.0 - production on 星期六 3月 6 10:30:17 2010copyright (c) 1982, 2005, oracle. all rights reserved.连接到: oracle database 10g enterprise edition release 10.2.0.1.0 - productionwith the partitioning, olap and data mining options已导出 zhs16gbk 字符集和 al16utf16 nchar 字符集. 正在导出

12、 pre-schema 过程对象和操作. 正在导出用户 a 的外部函数库名. 导出 public 类型同义词. 正在导出专用类型同义词. 正在导出用户 a 的对象类型定义即将导出 a 的对象. 正在导出数据库链接. 正在导出序号. 正在导出簇定义. 即将导出 a 的表通过常规路径. . 正在导出表 a导出了 49745 行. 正在导出同义词. 正在导出视图. 正在导出存储过程. 正在导出运算符. 正在导出引用完整性约束条件. 正在导出触发器. 正在导出索引类型. 正在导出位图, 功能性索引和可扩展索引. 正在导出后期表活动. 正在导出实体化视图. 正在导出快照日志. 正在导出作业队列. 正在导

13、出刷新组和子组. 正在导出维. 正在导出 post-schema 过程对象和操作. 正在导出统计信息成功终止导出, 没有出现警告。导出某个用户下所有的对象。c:documents and settingsliexp a/a file=e:oracleproduct10.2.0oradataora10gdatafilea.dmp full=y log=e:oracleproduct10.2.0oradataora10gdatafileorder.logexport: release 10.2.0.1.0 - production on 星期六 3月 6 10:33:33 2010copyrigh

14、t (c) 1982, 2005, oracle. all rights reserved.连接到: oracle database 10g enterprise edition release 10.2.0.1.0 - productionwith the partitioning, olap and data mining optionsexp-00023: 必须是 dba 才能执行完整数据库或表空间导出操作在导出整个数据库以前，所连接的用户必须具有exp_full_database的系统权限sql grant exp_full_database to a;授权成功。c:documents

15、 and settingsliexp a/a file=e:oracleproduct10.2.0oradataora10gdatafilea.dmp full=y log=e:oracleproduct10.2.0oradataora10gdatafileorder.log连接到: oracle database 10g enterprise edition release 10.2.0.1.0 - productionwith the partitioning, olap and data mining options已导出 zhs16gbk 字符集和 al16utf16 nchar 字符

16、集即将导出整个数据库. 正在导出表空间定义. 正在导出概要文件. 正在导出用户定义. 正在导出角色. 正在导出资源成本. 正在导出回退段定义. 正在导出数据库链接. 正在导出序号. 正在导出目录别名. 正在导出上下文名称空间. 正在导出外部函数库名. 导出 public 类型同义词. 正在导出专用类型同义词. 正在导出对象类型定义. 正在导出系统过程对象和操作. 正在导出 pre-schema 过程对象和操作. 正在导出簇定义. 即将导出 system 的表通过常规路径. . 正在导出表 def$_aqcall导出了 0 行. . 正在导出表 def$_aqerror导出了 0 行. . 正在

17、导出表 def$_calldest导出了 0 行. . 正在导出表 def$_defaultdest导出了 0 行. . 正在导出表 def$_destination导出了 0 行. . 正在导出表 def$_error导出了 0 行。使用参数文件进行导出c:documents and settingsliexp parfile=e:oracleproduct10.2.0oradataora10gdatafileexp.txt使用exp命令进行导出工作时，可以采取以下两种方式完成。1、传统路径方式exp采用sql语句的方式把要导出的表的数据检索出来，然后通过网络，将这些数据传输到客户端，并在客

18、户端上生成转储文件。既然是sql语句的方式，就需要在shared_pool中解析生成执行计划以后，将数据缓存到buffer中，然后传递到客户端，该方式是默认的导出方式。2、直接路径方式在这种方式下，exp不采用sql的方式，而是直接获得数据块，并绕过shared_pool和buffer cache。将数据块里的数据直接抽出来以后直接传递到客户端上，这种方式相对于传统路径来说，速度要快很多。在导出时，加上direct=y则表示进行直接路径导出，该方式有一些限制。比如不能导出含有lob、bfile等大对象的表等。c:documents and settingsliexp a/a file=e:or

19、acleproduct10.2.0oradataora10gdatafilea.dmp full=y direct=y log=e:oracleproduct10.2.0oradataora10gdatafileorder.log导入工具：导出工具生成的转储文件只能被导入工具所读取1、表模式：将转储文件中指定的表导入到目标数据库2、用户模式：将转储文件中指定的用户导入到目标数据库中3、数据库模式：将转储文件（该转储文件必须是以数据库模式导出的）中所有的数据都导入到目标数据库4、可传输表空间：将转储文件（该转储文件必须是以可传输表空间模式导出的）中的表空间的元数据导入到目标数据库c:docume

20、nts and settingsliimp help=yimport: release 10.2.0.1.0 - production on 星期六 3月 6 11:16:51 2010copyright (c) 1982, 2005, oracle. all rights reserved.通过输入 imp 命令和您的用户名/口令, 导入操作将提示您输入参数: 例如: imp scott/tiger或者, 可以通过输入 imp 命令和各种参数来控制导入的运行方式。要指定参数, 您可以使用关键字: 格式: imp keyword=value 或 keyword=(value1,value2,.

21、,valuen) 例如: imp scott/tiger ignore=y tables=(emp,dept) full=n 或 tables=(t1:p1,t1:p2), 如果 t1 是分区表userid 必须是命令行中的第一个参数。关键字 说明 (默认值) 关键字 说明 (默认值)-userid 用户名/口令 full 导入整个文件 (n)buffer 数据缓冲区大小 fromuser 所有者用户名列表file 输入文件 (expdat.dmp) touser 用户名列表show 只列出文件内容 (n) tables 表名列表ignore 忽略创建错误 (n) recordlength i

22、o 记录的长度grants 导入权限 (y) inctype 增量导入类型indexes 导入索引 (y) commit 提交数组插入 (n)rows 导入数据行 (y) parfile 参数文件名log 屏幕输出的日志文件 constraints 导入限制 (y)destroy 覆盖表空间数据文件 (n)indexfile 将表/索引信息写入指定的文件skip_unusable_indexes 跳过不可用索引的维护 (n)feedback 每 x 行显示进度 (0)toid_novalidate 跳过指定类型 id 的验证filesize 每个转储文件的最大大小statistics 始终导入

23、预计算的统计信息resumable 在遇到有关空间的错误时挂起 (n)resumable_name 用来标识可恢复语句的文本字符串resumable_timeout resumable 的等待时间compile 编译过程, 程序包和函数 (y)streams_configuration 导入流的一般元数据 (y)streams_instantiation 导入流实例化元数据 (n)下列关键字仅用于可传输的表空间transport_tablespace 导入可传输的表空间元数据 (n)tablespaces 将要传输到数据库的表空间datafiles 将要传输到数据库的数据文件tts_owner

24、s 拥有可传输表空间集中数据的用户成功终止导入, 没有出现警告。sql drop table a.a;表已删除。sql select * from a.a;select * from a.a *第 1 行出现错误:ora-00942: 表或视图不存在c:documents and settingsliimp a/a file=e:oracleproduct10.2.0oradataora10gdatafilea.dmp tables=a.aimport: release 10.2.0.1.0 - production on 星期六 3月 6 11:49:24 2010copyright (c)

25、 1982, 2005, oracle. all rights reserved.连接到: oracle database 10g enterprise edition release 10.2.0.1.0 - productionwith the partitioning, olap and data mining options经由常规路径由 export:v10.02.01 创建的导出文件imp-00013: 只有 dba 才能导入由其他 dba 导出的文件imp-00000: 未成功终止导入出现这个问题的原因是因为前面为用户授予了exp_full_database权限，造成此用户导出的

26、数据具有dba身份。解决方法有几种1， 授予对应的权限，imp_full_database2， 收回exp_full_database，然后重新导出，再导入非要使用dba身份导入呢？c:documents and settingsliimp sys/oracle as sysdba file=e:oracleproduct10.2.0oradataora10gdatafilea.dmp tables=(a,a)结果就是在sys表空间下增加了一个新的a，将数据导入到了sys用户下，a用户下没有导入授予对应权限后就可以导入了sql grant imp_full_database to a;授权成功

27、。c:documents and settingsliimp a/a file=e:oracleproduct10.2.0oradataora10gdatafilea.dmp tables=(a,a)import: release 10.2.0.1.0 - production on 星期六 3月 6 12:07:11 2010copyright (c) 1982, 2005, oracle. all rights reserved.连接到: oracle database 10g enterprise edition release 10.2.0.1.0 - productionwith t

28、he partitioning, olap and data mining options经由常规路径由 export:v10.02.01 创建的导出文件已经完成 zhs16gbk 字符集和 al16utf16 nchar 字符集中的导入. 正在将 a 的对象导入到 a. 正在将 a 的对象导入到 a. . 正在导入表 a导入了 49751 行成功终止导入, 没有出现警告。c:documents and settingsliimp b/b file=e:a.dmp fromuser=a touser=bimport: release 10.2.0.1.0 - production on 星期六

29、 3月 6 21:22:50 2010copyright (c) 1982, 2005, oracle. all rights reserved.连接到: oracle database 10g enterprise edition release 10.2.0.1.0 - productionwith the partitioning, olap and data mining options经由常规路径由 export:v10.02.01 创建的导出文件警告: 这些对象由 a 导出, 而不是当前用户已经完成 zhs16gbk 字符集和 al16utf16 nchar 字符集中的导入. 正在

30、将 a 的对象导入到 b. . 正在导入表 a导入了 49745 行成功终止导入, 没有出现警告。c:documents and settingsliimp b/b file=e:b.dmp fromuser=b touser=aimport: release 10.2.0.1.0 - production on 星期六 3月 6 21:23:05 2010copyright (c) 1982, 2005, oracle. all rights reserved.连接到: oracle database 10g enterprise edition release 10.2.0.1.0 - p

31、roductionwith the partitioning, olap and data mining options经由常规路径由 export:v10.02.01 创建的导出文件已经完成 zhs16gbk 字符集和 al16utf16 nchar 字符集中的导入imp-00007: 必须为 dba 才能将对象导入另一个用户帐户imp-00000: 未成功终止导入c:documents and settingsliimp sys/oracle as sysdba file=e:b.dmp fromuser=b touser=aimport: release 10.2.0.1.0 - pro

32、duction on 星期六 3月 6 21:23:23 2010copyright (c) 1982, 2005, oracle. all rights reserved.连接到: oracle database 10g enterprise edition release 10.2.0.1.0 - productionwith the partitioning, olap and data mining options经由常规路径由 export:v10.02.01 创建的导出文件警告: 这些对象由 b 导出, 而不是当前用户已经完成 zhs16gbk 字符集和 al16utf16 nch

33、ar 字符集中的导入. 正在将 b 的对象导入到 a. . 正在导入表 b导入了 49746 行成功终止导入, 没有出现警告。导入时如果报错，那么使用日志进行排错。修改错误以后，删除用户，然后继续进行导入，一直到没有错误为止。注意导入和导出操作中没有分号做结尾，不要与sql语句混淆在导入以前要进行删除用户的操作。在导出以前最好进行数据库对象的查看，然后进行导出。导入的时候最好是另外一个新的数据库。如果有些错误可以忽略，那么可以使用下面的参数ignore=y。使用ignore表示如果创建表的时候，发现该表已经存在，则忽略该错误，继续进行下面的步骤，这样就出现数据的累加。c:documents a

34、nd settingsliimp a/a file=e:b.dmp fromuser=b touser=aimport: release 10.2.0.1.0 - production on 星期六 3月 6 21:31:40 2010copyright (c) 1982, 2005, oracle. all rights reserved.连接到: oracle database 10g enterprise edition release 10.2.0.1.0 - productionwith the partitioning, olap and data mining options经由

35、常规路径由 export:v10.02.01 创建的导出文件警告: 这些对象由 b 导出, 而不是当前用户已经完成 zhs16gbk 字符集和 al16utf16 nchar 字符集中的导入. 正在将 b 的对象导入到 aimp-00015: 由于对象已存在, 下列语句失败: create table b (b number(*,0) pctfree 10 pctused 40 initrans 1 maxtra ns 255 storage(initial 720896 freelists 1 freelist groups 1 buffer_pool def ault) logging n

36、ocompress成功终止导入, 但出现警告。c:documents and settingsliimp a/a file=e:b.dmp fromuser=b touser=a ignore=yimport: release 10.2.0.1.0 - production on 星期六 3月 6 21:31:53 2010copyright (c) 1982, 2005, oracle. all rights reserved.连接到: oracle database 10g enterprise edition release 10.2.0.1.0 - productionwith the

37、 partitioning, olap and data mining options经由常规路径由 export:v10.02.01 创建的导出文件警告: 这些对象由 b 导出, 而不是当前用户已经完成 zhs16gbk 字符集和 al16utf16 nchar 字符集中的导入. 正在将 b 的对象导入到 a. . 正在导入表 b导入了 49746 行成功终止导入, 没有出现警告。sql select count(*) from a.b; count(*)- 99492在执行导入命令的过程中，按照下面的顺序导入表、表里的数据、以及其他一些对象。1、创建表结构2、导入表的数据3、创建索引4、导

38、入触发器5、对导入的表启用完整性约束6、建立所有位图、函数、以及其他索引即使具有dba权限，但是也不能导出sys用户的对象sql grant sysdba to a;授权成功。c:documents and settingsliexp a/a file=e:a.dmp owner=aexport: release 10.2.0.1.0 - production on 星期六 3月 6 21:36:55 2010copyright (c) 1982, 2005, oracle. all rights reserved.连接到: oracle database 10g enterprise edi

39、tion release 10.2.0.1.0 - productionwith the partitioning, olap and data mining options已导出 zhs16gbk 字符集和 al16utf16 nchar 字符集. 正在导出 pre-schema 过程对象和操作. 正在导出用户 a 的外部函数库名. 导出 public 类型同义词. 正在导出专用类型同义词. 正在导出用户 a 的对象类型定义即将导出 a 的对象. 正在导出数据库链接. 正在导出序号. 正在导出簇定义. 即将导出 a 的表通过常规路径. . 正在导出表 b导出了 99492 行. 正在导出同义

40、词. 正在导出视图. 正在导出存储过程. 正在导出运算符. 正在导出引用完整性约束条件. 正在导出触发器. 正在导出索引类型. 正在导出位图, 功能性索引和可扩展索引. 正在导出后期表活动. 正在导出实体化视图. 正在导出快照日志. 正在导出作业队列. 正在导出刷新组和子组. 正在导出维. 正在导出 post-schema 过程对象和操作. 正在导出统计信息成功终止导出, 没有出现警告。sql conn a/a已连接。sql select * from user_objects;object_name-subobject_name object_id data_object_id object

41、_type- - - -created last_ddl_time timestamp status t g s- - - - - - -b 51351 51351 table06-3月 -10 06-3月 -10 2010-03-06:21:31:37 valid n n na用户下面没有sys表空间中的对象c:documents and settingsliexp a/a as sysdba file=e:a.dmp owner=aexport: release 10.2.0.1.0 - production on 星期六 3月 6 21:46:27 2010copyright (c) 1

42、982, 2005, oracle. all rights reserved.连接到: oracle database 10g enterprise edition release 10.2.0.1.0 - productionwith the partitioning, olap and data mining options已导出 zhs16gbk 字符集和 al16utf16 nchar 字符集即将导出指定的用户. 正在导出 pre-schema 过程对象和操作. 正在导出用户 a 的外部函数库名. 导出 public 类型同义词. 正在导出专用类型同义词. 正在导出用户 a 的对象类型

43、定义即将导出 a 的对象. 正在导出数据库链接. 正在导出序号. 正在导出簇定义. 即将导出 a 的表通过常规路径. . 正在导出表 b导出了 99492 行. 正在导出同义词. 正在导出视图. 正在导出存储过程. 正在导出运算符. 正在导出引用完整性约束条件. 正在导出触发器. 正在导出索引类型. 正在导出位图, 功能性索引和可扩展索引. 正在导出后期表活动. 正在导出实体化视图. 正在导出快照日志. 正在导出作业队列. 正在导出刷新组和子组. 正在导出维. 正在导出 post-schema 过程对象和操作. 正在导出统计信息成功终止导出, 没有出现警告。c:documents and se

44、ttingsliexp a/a as sysdba file=e:full.dmp full=yexport: release 10.2.0.1.0 - production on 星期六 3月 6 21:44:18 2010copyright (c) 1982, 2005, oracle. all rights reserved.连接到: oracle database 10g enterprise edition release 10.2.0.1.0 - productionwith the partitioning, olap and data mining options已导出 zhs

45、16gbk 字符集和 al16utf16 nchar 字符集即将导出整个数据库. 正在导出表空间定义. 正在导出概要文件. 正在导出用户定义. 正在导出角色. 正在导出资源成本。正在导出表 cwm2$mrall_awviews导出了 0 行 正在导出表 cwm2$mrall_catalogs导出了 0 行 正在导出表 cwm2$mrall_catalog_entity_uses导出了 0 行 正在导出表 cwm2$mrall_cube_measures导出了 0 行 正在导出表 cwm2$mrall_cwm1_aggop导出了 0 行 正在导出表 cwm2$mrall_cwm1_aggord导

46、出了 0 行 正在导出表 cwm2$mrall_descriptors导出了 41 行 正在导出表 cwm2$mrall_dim_attributes导出了 0 行 正在导出表 cwm2$mrall_dim_hiers导出了 0 行 正在导出表 cwm2$mrall_dim_hier_level_uses导出了 0 行 正在导出表 cwm2$mrall_dim_level_attr_maps导出了 0 行 正在导出表 cwm2$mrall_entity_desc_uses导出了 0 行 正在导出表 cwm2$mrall_entity_ext_parms导出了 0 行 正在导出表 cwm2$mr

47、all_entity_parameters导出了 0 行 正在导出表 cwm2$mrall_facttblfctmaps导出了 0 行 正在导出表 cwm2$mrall_facttblkeymaps导出了 0 行 正在导出表 cwm2$mrall_hierdims导出了 0 行 正在导出表 cwm2$mrall_hierdim_keycol_map导出了 0 行 正在导出表 cwm2$mrall_hier_custom_sort导出了 0 行 正在导出表 cwm2$mrall_join_key_col_uses导出了 0 行。尽量少使用全库的导入和导出。而是分解成用户的导入和导出。具体见导出日

48、志文件，所有的用户都进行了导出，包括system等用户。可以测试一下sys用户是否也进行了导出。具体是sys下面建立一个表，看是否导出。可传输表空间1、导入和导出的方式，先将要迁移的数据通过exp工具导出，生成转储文件，然后通过imp工具读取导出文件，并导入到目标数据库。缺点：如果要迁移的数据量很大的话，则该方式会很慢可以使用表空间传输的方式来迁移数据只包含表空间的元数据，也就是表空间里所有对象的定义信息表空间a数据文件1数据文件2数据文件n表空间a数据文件n数据文件2数据文件1导出文件导出文件导出导入拷贝拷贝拷贝拷贝要传输的表空间的元数据指的是要传输的表空间的所有对象的定义信息。导出和导入的速度非常的快。具体步骤如下1、检查要传输的表空间是否是自我包含的（self-contained）具体含义指的是要传输的表空间里的对象，没有引用位于其他表空间里的对象。例如：要传输的表空间example里某个表的索引位于ind