Oracle EXP工具QUERY参数使用方法和限制条件_MySQL, Oracle及数据库讨论区_Weblogic技术|Tuxedo技术|中间件技术|Oracle论坛|JAVA论坛|Linux/Unix技术|hadoop论坛_联动北方技术论坛  
网站首页 | 关于我们 | 服务中心 | 经验交流 | 公司荣誉 | 成功案例 | 合作伙伴 | 联系我们 |
联动北方-国内领先的云技术服务提供商
»  游客             当前位置:  论坛首页 »  自由讨论区 »  MySQL, Oracle及数据库讨论区 »
总帖数
2
每页帖数
101/1页1
返回列表
0
发起投票  发起投票 发新帖子
查看: 396 | 回复: 1   主题: Oracle EXP工具QUERY参数使用方法和限制条件        上一篇   下一篇 
landy
注册用户
等级:新兵
经验:61
发帖:72
精华:0
注册:2011-12-31
状态:离线
发送短消息息给landy 加好友    发送短消息息给landy 发消息
发表于: IP:您无权察看 2015-4-8 17:56:31 | [全部帖] [楼主帖] 楼主

摘要:日常工作中我们经常使用EXP/IMP工具进行数据归档、数据迁移、数据库升级、备份数据库、回收碎片等工作。Exp导出的dump文件是一个二进制文件,不可手工编辑,因为会破坏数据。如果单论速度讲IMP要比EXP快些,这也表明了我们在迁移数据时压力一般在EXP这边。

经典使用场景:

(1)Oracle 9i版本

(2)同版本数据库之间

(3)不同版本数据库之间

(4)同版本操作系统之间

(5)不同版本操作系统之间

(6)数据库备份、升级、迁移

(7)从一个SCHEMA传送到另一个SCHEMA

(8)dump文件可跨平台迁移

(9)dump文件import时采用向上兼容,例如 Oracle10g的dump文件可以导入oracle11g

EXP工具本身具有很多参数,每个参数又都有其特定的功能,今天我们来讲解QUERY参数,为什么选择这个参数呢?它有什么功能呢?下面来给大家一一解答。

oracle@linux-db02:~> exp help=y
Keyword   Description (Default)     Keyword      Description (Default)
--------------------------------------------------------------------------
USERID    username/password        FULL       export entire file(N)
BUFFER    size of data buffer       OWNER      list of ownerusernames
FILE     output files (EXPDAT.DMP)   TABLES      list of table names
COMPRESS  import into one extent (Y) RECORDLENGTH   length of IO record
GRANTS    export grants (Y)       INCTYPE      incremental exporttype
INDEXES   export indexes (Y)       RECORD       track incr. export (Y)
DIRECT    direct path (N)         TRIGGERS     export triggers (Y)
LOG      log file of screen output STATISTICS   analyze objects(ESTIMATE)
ROWS     export data rows (Y)      PARFILE      parameter filename
CONSISTENT cross-table consistency(N) CONSTRAINTS  export constraints (Y)
OBJECT_CONSISTENT    transaction set to read only during objectexport (N)
FEEDBACK      display progress every x rows (0)
FILESIZE       maximum size of each dump file
FLASHBACK_SCN      SCN used to set session snapshot backto
FLASHBACK_TIME     time used to get the SCN closest to thespecified time
QUERY      selectclause used to export a subset of a table
RESUMABLE       suspend when a space related erroris encountered(N)
RESUMABLE_NAME     text string used to identify resumablestatement
RESUMABLE_TIMEOUT    wait time for RESUMABLE
TTS_FULL_CHECK     perform full or partial dependency checkfor TTS
VOLSIZE       number of bytes to write to eachtape volume
TABLESPACES       list of tablespaces to export
TRANSPORT_TABLESPACE export transportabletablespace metadata (N)
TEMPLATE        template name which invokes iASmode export
Export terminated successfully withoutwarnings.


我们从EXP工具帮助信息中可以明确看到对其的解释说明,就是在导出表的过程中可以针对表数据的某一个子集进行导出,过滤掉我们不需要的数据,选择出我们需要的数据。就像一个筛子,只把符合条件的数据导出。知道了这个功能对我们平时测试数据,迁移数据都有重大的帮助。效率将大大提高。

实验

1.创建leo1表,表中有12877条记录

 SFCP@base> create table leo1 as select *from dba_objects;
Table created.
SFCP@base> select count(*) from leo1;
COUNT(*)
----------------
12877


2.第一次导出全部表数据,不使用query参数

oracle@linux-db02:~/exp_data> exp sfcp/sfcp file=/home/oracle/exp_data/20140409leo1.dmp tables=leo1 rows=y direct=y
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With thePartitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set andAL16UTF16 NCHAR character set
About to export specified tables via DirectPath ...
. . exporting table            LEO1   12877 rows exported
Export terminated successfully withoutwarnings.


导出完毕,LEO1表的数据有12877行全部导出

3.第二次导出object_id < 6000的数据行,差不多占全表的一半

oracle@linux-db02:~/exp_data> exp sfcp/sfcp file=/home/oracle/exp_data/20140409leo1-2.dmp tables=leo1 rows=y query=\"where object_id \< 6000\"
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With thePartitioning, OLAP, Data Mining and Real Application Testing options
Export done in ZHS16GBK character set andAL16UTF16 NCHAR character set
About to export specified tables viaConventional Path ...
. . exporting table          LEO1       5806 rows exported


导出完毕,满足条件的有5806数据行

4.对比一下两个dump文件的大小

-rw-r--r-- 1 oracle oinstall 581632  4月  9 17:01 20140409leo1-2.dmp
-rw-r--r-- 1 oracle oinstall 1302528  4月  9 16:54 20140409leo1.dmp


从导出文件的容量上也可以很容易的分辨出哪个是全表数据文件,哪个是部分表数据文件,这对有这方面需求的业务是一个很好用的方法。

限制条件

1.QUERY参数必须和TABLES参数一起使用,仅支持表级导出模式

oracle@linux-db02:~/exp_data> exp sfcp/sfcp file=/home/oracle/exp_data/20140409leo1-2.dmp rows=y query=\"whereobject_id \< 6000\"
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
EXP-00035: QUERYparameter valid only for table mode exports
EXP-00000: Export terminated unsuccessfully


2.QUERY参数不能同时兼容DIRECT参数,两者不能同时使用

oracle@linux-db02:~/exp_data> exp sfcp/sfcp file=/home/oracle/exp_data/20140409leo1-3.dmp tables=leo1 rows=y direct=y query=\"whereobject_id \< 6000\"
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
EXP-00071: QUERYparameter not compatible with Direct Path export
EXP-00000: Export terminated unsuccessfully


DIRECT参数说明:直接路径方式导出,就是直接使用direct io来读取数据文件,不经过buffercache,导出数据比较快。ORACLE会绕过SQL语句处理引擎,直接从数据文件中读取数据,然后写入导出文件。如果表中包含某些特定字段数据类型,比如大对象类型,碰到这样情况的时候,ORACLE会自动切换到常规的导出方式,并在导出日志中记录。

3.QUERY参数仅支持同构表同时导出,不支持异构表同时导出

SFCP@base> create table leo2 as select *from dba_objects;   创建第二张表
Table created.
oracle@linux-db02:~/exp_data> exp sfcp/sfcp file=/home/oracle/exp_data/20140409leo1-4.dmp tables=leo1,leo2 rows=yquery=\"where object_id \< 6000\"
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
Export done inZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables viaConventional Path ...
. . exporting table          LEO1       5806 rows exported
. . exporting table          LEO2       5806 rows exported
Export terminated successfully withoutwarnings.


答:LEO1和LEO2是同构表,因此可以把两张表中符合条件的数据行全部导出

SFCP@base> create table leo3 as select *from dba_tables;    创建第三张表
Table created.
oracle@linux-db02:~/exp_data> exp sfcp/sfcp file=/home/oracle/exp_data/20140409leo1-5.dmp tables=leo1,leo3 rows=yquery=\"where object_id \< 6000\"
Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
Export done inZHS16GBK character set and AL16UTF16 NCHAR character set
About to export specified tables viaConventional Path ...
. . exporting table          LEO1       5806 rows exported
. . exporting table          LEO3
EXP-00056: ORACLE error904 encountered
ORA-00904:"OBJECT_ID": invalid identifier
Export terminated successfully withwarnings


答:LEO1和LEO3是异构表,因此只把符合条件的表导出,不符合条件的表报错

4.QUERY参数不支持部分列导出

官方文档:If a table ismissing the columns specified in the QUERY clause,an error message will beproduced,and no rows will be exported for the offending table.

如果在QUERY参数中仅指定部分列导出,将会产生一个错误->没有行被导出(只导出表结构,空表)。这是因为Oracle是基于行存储的数据库,只能做水平分割,不支持垂直分割。在双引号之间只可以写入where子句内容。

正确: query=\"whereobject_name=\'BFILE\'\"

query = \" where object_id\<1000 \"
Example:
exp sfcp/sfcp file=20140409leo1-6.dmptables=leo1 rows=y query=\"where object_name=\'BFILE\'\"
exp sfcp/sfcp file=20140409leo1-7.dmptables=leo1 rows=y query = \" where object_id\<1000 \"


错误:query="select object_name,object_id,object_type from leo1"

Example:
exp sfcp/sfcp file=20140409leo1-2.dmptables=leo1 rows=y query=\"select object_name,object_id,object_type from leo1\"


错误信息:EXP-00056: ORACLE error 933 encountered

ORA-00933: SQL command not properly ended


5.QUERY参数在命令行中使用时注意转义字符修饰

因为不同的操作系统commend line中特殊字符的定义是不同的,有些字符必须使用转义字符修饰才能表达出来,而且不同操作系统的转义字符又有所区别,应该根据不同的操作系统采用不同的方法。

Example


Windows系统

exp sfcp/sfcp file=20140409leo1-6.dmp tables=leo1 rows=y query=""" where object_id<1000 """


windows中,需要在WHERE语句的两端使用三个双引号

Linux系统

exp sfcp/sfcp file=20140409leo1-6.dmp tables=leo1 rows=y query= \" where object_id\<1000 \"


linux中,需要在WHERE语句特殊字符前添加\转义字符

问:有没有好的方法可以让QUERY参数在不同操心系统命令行中不进行转义

答:使用PARFILE参数可以不考虑操作系统平台而使用完全相同的方法

(1)定义参数文件PARFILE

vim leo1.par
query="where object_id<1000"


在参数文件leo1.par中,可以直接填写想要实现的where子句,不用再拿转义字符进行修饰表达

(2)exp使用参数文件导出

oracle@linux-db02:~/exp_data> exp sfcp/sfcp file=/home/oracle/exp_data/20140409leo1-4.dmp tables=leo1 rows=y parfile=/home/oracle/exp_data/leo1.par
Connected to: Oracle Database 10gEnterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Miningand Real Application Testing options
Export done in ZHS16GBK character set andAL16UTF16 NCHAR character set
About to export specified tables viaConventional Path ...
. . exporting table          LEO1        953 rows exported
Export terminated successfully withoutwarnings.


OK,我们使用PARFILE方法完美解决了QUERY参数需要转义的问题。这里也建议大家在使用QUERY参数时结合PARFILE参数一起来使用,减少测试时间和出错的概率。

小结:到此我们把QUERY参数的应用场景和注意事项一一为大家进行了详细的解说,EXP工具的每一个参数都有其相应功能,我们在使用时要做到具体问题具体分析、灵活运用。

--转自 北京联动北方科技有限公司




赞(0)    操作        顶端 
联动大白
注册用户
等级:新兵
经验:79
发帖:0
精华:0
注册:2015-5-27
状态:离线
发送短消息息给联动大白 加好友    发送短消息息给联动大白 发消息
发表于: IP:您无权察看 2018-10-12 0:30:00 | [全部帖] [楼主帖] 2  楼

为了方便大家阅读,我对文章中错误号来解释一下吧!

Error Id: EXP-00000

Title: Export terminated unsuccessfully

Description:

Export terminated unsuccessfully

Action:

Look up the accompanying Oracle message in the ORA message chapters of this manual, and take appropriate action.

Cause:

Export encountered an Oracle error.


Error Id: EXP-00035

Title: QUERY parameter valid only for table mode exports

Description:

QUERY parameter valid only for table mode exports

Action:

If you want to select a subset of rows for a table, you must export the table independently with a table mode export. Issue a table mode export command that specifies the table name and the query you want to execute during export.

Cause:

You specified the QUERY parameter in an export command, but you are not performing a table mode export. The QUERY parameter cannot be used for a user mode export, a full export, nor a point in time recovery export.


Error Id: EXP-00071

Title: QUERY parameter not compatible with Direct Path export

Description:

QUERY parameter not compatible with Direct Path export

Action:

Re-issue the export command with DIRECT=N or omit the DIRECT parameter from the command line.

Cause:

You specified a value for the QUERY parameter for a direct path export. The QUERY parameter cannot be used with a direct path export.


也许你已明白,但对一个人有用也是我存在的理由!^_^ By:持之以恒的大白

-- 来自: 北京联动北方科技有限公司



赞(0)    操作        顶端 
总帖数
2
每页帖数
101/1页1
返回列表
发新帖子
请输入验证码: 点击刷新验证码
您需要登录后才可以回帖 登录 | 注册
技术讨论