教你如何成为Oracle 10g OCP - 第十九章 数据迁移

主要内容
1. exp/imp的原理及使用
2. 如何进行可传输表空间的操作，特别是进行跨平台表空间传输
3. expdp/impdp的原理及使用
4. 如何使用外部表
5. 如何进行可传输数据库的操作 

1. exp/imp

exp/imp 原理:  它是一个位于客户端的程序(在DB Server及远程客户端都能操作) , 能跨平台进行导出导入操作，
普通的exp出来的dmp文件包括两部分内容，一部分是文件头，还有一部分是表和其它对象的定义,如果是表 ，则
还包括二进制的行数据(如果选择rows=y)，这种格式与实际块中存储的行格式一致(可以通过特殊工具比如UltraEdit
查看到，如下，当然这些语句前面还有很多的二进制的字符)。 

基本可以看出，就是将建立Objects的脚本导出，数据部分采用insert into的方式进行记录。 

"
CREATE TABLE "PF_TEMP_EXCEL" ("COL1" VARCHAR2(36), "COL2" VARCHAR2(36), "COL3" VARCHAR2(36), "COL4" VARCHAR2(36))  PCTFREE 10 PCTUSED 0

INITRANS 1 MAXTRANS 255 STORAGE(INITIAL 65536 FREELISTS 1 FREELIST GROUPS 1) TABLESPACE "USERS" LOGGING NOCOMPRESS
INSERT INTO

"PF_TEMP_EXCEL" ("COL1", "COL2", "COL3", "COL4") VALUES (:1, :2, :3, :4)

exp/imp 是跨平台的数据迁移工具，因为它的导出是一种逻辑模式，而不是物理的，也就是说它是以建立物件脚本以及
SQL语句插入数据为存储格式的，而不是block为单位来复制的(Standby就是以Block为单位来进行备份的)，所以和
平台没有关系。exp/imp可以作为数据备份的补充手段(逻辑备份)。  

备注： Oracle10g或以后可以使用expdp/impdp, 但是exp/imp还是可以使用。后面会详细讲到expdp/impdp.  

 

exp有四种模式：

A. 表模式
B. 用户模式(Schema)
C. 数据库模式(可以导出除了用户sys以外的数据库里的所有对象)
D. 可传输表空间模式(导出某个指定的表空间中的所有对象) 

 

$ exp  help=y 可以得到帮助
C:\>exp help=y

 

 

 

导出某个用户(hrm)下指定的表(表模式),其中table所在用户就是hrm,如果不是需要加入schema.

$ exp hrm/hrm file=emp_dept.dmp  tables=(employees,departments)  log=empdept.log

$ exp hrm/hrm file=emp_dept.dmp  tables=(oe.employees,oe.departments)  log=empdept.log
(其中要求hrm用户有权限导出导入，且hrm用户有查看oe用户下这两个表的权限) 

导出某个用户下的所有对象(对象包括：table,procedure,index,grant,trigger,constraint等)
$ exp  sys/sysoracle file=hrm.dmp  wner=hrm   log=hrm.log 

 

 

导出整个数据库： 连接用户必须有 exp_full_database的系统权限

SQL> grant exp_full_database to  hrm ; 
$ exp  hrm/hrm  file=full_db.dmp  full=y  log=full_db.log 

 

 

导出时的输入参数可以使用一个文件代替。

$vi emp.par

userid=hrm/hrm
file=emp.dmp
tables=(emp,dept)
log=emp.log 

执行导出命令： $ exp  parfile=emp.par 

 

 

 

导出有两种方式：  A. 传统路径导出 ， B. 直接路径导出

A. 传统路径导出： exp采用SQL语句的方式把要导出的表的数据检索出来，通过网络将数据传输到客户端，在客户端生成
转储文件，由于是SQL语句方式，就需要在shared pool中解析执行计划，将数据缓存到buffer cache中，然后传递到客户
端，这是默认的导出方式。

B. 直接路径导出： 采用加入参数 direct=y 来设置。这种exp不采用传统的SQL读取数据的方式，而是直接获得数据块，并
绕过shared pool 和buffer cache , 将数据块里面的数据抽取出来以后直接传输给客户端上，这种方式相对传统方式会快很多，但是这种方式有一些限制，比如

不能导出含有 LOB,BFILE等大对象的表，还有含有Query子句的exp也不能使用direct=y  . 

传统路径导出： 要经过SQL语句，shared pool, buffer cache等一系列SQL必须的过程，且数据会被移动到一个工作区，行被提出来，检查和Query查询中的

where子句(如果有的话)是否匹配。所以速度慢一些。

直接路径导出： Specifying DIRECT=y causes Export to extract data by reading the data directly, bypassing the SQL command-processing layer

(evaluating buffer). This method can be much faster than a conventional path Export. 

 

 

 
imp导入同样有四种模式 。
$ imp hr/hr file=emp.dmp table=(emp,dept) log=emp.log ignore=y
$ imp sys/sysora fromuser=hrm  touser=hrkq  file=emp.dmp table=(emp,dept) ignore=y  log=emp.log 
导入时也可以使用参数文件 parfile .

 

导入物件的顺序：

A. 创建表结构
B. 导入表的数据
C. 创建索引
D. 导入触发器
E. 对导入的表启用完整性约束
F. 建立所有位图，函数以及其他索引 

如果出现table已经存在等错误，那么imp就不能正常进行了，我们可以使用ignore=y来解决。

 

 

exp/imp的注意事项：

1. Conventional path Export.
    -------------------------
传统导出模式使用SQL SELECT语句抽取表数据。将数据从磁盘中读入到buffer cache缓冲区中，并应用SQL表达式，将纪录返回给导出客户端，然后写到到处文件

。

2. Direct path Export.
    -------------------
直接导出模式，数据直接从磁盘中读取到导出session的UGA中，跳过了SQL命令处理层。避免了不必要的数据转换， 然后将纪录返回给导出客户端，然后写到到

处文件   跳过了SQL命令处理层表示DIRECT导出不支持QUERY选项。

性能
    1. Direct Export 比Conventional Export要快很多。在实际应用中，如果直接模式导出需要时间N，传统模式导出则需要2*N到3*N之间。
    2. 当Direct Export 的时候设置大的RECORDLENGTH（length of IO record） 参数可以加快导出。最大64k，
    exp userid=system/manager full=y direct=y recordlength=65535
    file=exp_full.dmp log=exp_full.log
    imp userid=system/manager full=y recordlength=65535
    file=exp_full.dmp log=imp_full.log
    3. Direct 模式导出并不影响导入数据的速度；导入数据与传统模式花费时间一样。
    4. imp时适当调整参数buffer以及数据库undo大小以及UNDO_RETENTION时间 。
    5. exp时候加入 consistent=n  

限制
    ------------
    1. 导出表空间必须使用传统模式。
    2. 含有LOB对象的表不支持直接导出模式。
    3. 直接导出不支持QUERY.
    4. 直接导出模式使用RECORDLENGTH设置一次可以导出数据的量，传统模式使用buffer设置.
    5. 低版本直接导出模式要求导出客户端和数据库字符集设置一致。

    直接导出模式bug比传统模式要多，但由于其导出数据在性能上的优势，仍然要多加使用。

 

常用的一些exp/imp语句例子：

exp  "'sys/oracle  as sysdba'"  buffer=8192  tables=pdm626.streamdata  feedback=200  compress=n   filesize=20G  file=

(streamdata1,streamdata2)  log=stream    

exp  system/system  QUERY=\"WHERE streamid>=1 and streamid<2000\"   buffer=8192   tables=pdm626.streamdata   feedback=50  consistent=n 

compress=n    filesize=20G   log=pdm_streamdata  file=(pdmstream_1,pdmstream_2)     

exp  "'sys/oracle  as sysdba'"  buffer=8192  wner=cis  feedback=100  consistent=n compress=n   filesize=10G  log=cis 

imp  "'sys/oracle  as sysdba'"  fromuser=cis  touser=cis  file=cis_1.DMP  log=cisimp2  ignore=y 

 

exp  "'sys/oracle  as sysdba'"  buffer=8192  wner=pdmext  feedback=100  consistent=n  compress=n   filesize=10G  log=pdmext  

exp  "'sys/oracle  as sysdba'"  buffer=8192  wner=pdmext   feedback=100   consistent=n
compress=n    filesize=20G   log=pdmext_data  file=(pdmext_1,pdmext_2,pdmext_3,pdmext_4,pdmext_5)   

imp  "'sys/oracle  as sysdba'"  fromuser=pdmext  touser=pdmext  file=pdmext_1.DMP  log=pdmextimp_data  ignore=y

exp  system/system  QUERY=\"WHERE streamid>=100000 and streamid<150000\"   buffer=8192   tables=pdm626.streamdata   feedback=50  consistent=n  compress=n    filesize=20G   log=pdmstream3  file=(pdmstream3_1,pdmstream3_2) 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-620590/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-620590/