《Oracle EXP工具QUERY引數使用方法和限制條件》-使用場景-對比測試-可下載

更多精彩內容盡在

《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=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工具的每一個引數都有其相應功能，我們在使用時要做到具體問題具體分析、靈活運用。

     請點選下載

exp  query  direct  parfile  oracle9i

Leonarding劉盛

2014.04.09

北京&spring

分享技術~成就夢想

Blog：