《Oracle EXP工具QUERY参数使用方法和限制条件》
摘要：日常工作中我们经常使用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=y query=\"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工具的每一个参数都有其相应功能，我们在使用时要做到具体问题具体分析、灵活运用。
exp query direct parfile oracle9i