exp query 參數的格式

 

昨天需要按照條件匯出一個表，再匯入另一個資料庫，很自然想到了 exp 的 query 參數，本來以為可以很順利完成，結果 query
參數的格式怎麼也寫不對，因為時間緊迫，最後通過 dblink
直接插入了另一個資料庫。今天回過頭來再研究了一下，發現其實也不複雜，按照文檔還是能很好的解決的。

希望匯出測試表中日期為 2007-09-22 的一條資料：

SQL> select * from skytest;

       SID DT
---------- -----------------
         1 20070913 00:00:00
         2 20070914 00:00:00
         3 20070915 00:00:00
         4 20070916 00:00:00
         5 20070917 00:00:00
         6 20070918 00:00:00
         7 20070919 00:00:00
         8 20070920 00:00:00
         9 20070921 00:00:00
        10 20070922 00:00:00
        12 20070924 00:00:00

11 rows selected.

憑印象按照以下格式運行：

exp / tables=skytest file=test.dmp query=/"where dt=to_date(/'2007-09-22/',/'yyyy-mm-dd/')/"

結果報錯：

ksh: syntax error: `(' unexpected

其實文檔中講得很清楚：

exp scott/tiger TABLES=emp QUERY=/"WHERE job=/'SALESMAN/' and sal /<1600/"

Note:
Because
the value of the QUERY parameter contains blanks, most operating
systems require that the entire strings WHERE job=/'SALESMAN/' and
sal/<1600 be placed in double quotation marks or marked as a literal
by some method. Operating system reserved characters also need to be preceded by an escape character. See your Oracle operating system-specific documentation for information about special and reserved characters on your system.

所有作業系統保留字元都要使用轉義符號，看來 ( ) 也需要轉義。

正確的寫法：

oracle DBALNP01 > exp / tables=skytest file=test.dmp query=/"where dt=to_date/(/'2007-09-22/',/'yyyy-mm-dd/'/)/"

Export: Release 8.1.7.4.0 - Production on Wed Sep 12 04:30:45 2007

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
Export done in US7ASCII character set and US7ASCII NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                        SKYTEST          1 rows exported
Export terminated successfully without warnings.

簡潔的寫法：
如果環境變數設定合適，就不需要使用 to_date 函數了

oracle DBALNP01 > exp / tables=skytest file=test.dmp query=/"where dt=/'20070922/'/"

Export: Release 8.1.7.4.0 - Production on Wed Sep 12 04:25:56 2007

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
Export done in US7ASCII character set and US7ASCII NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                        SKYTEST          1 rows exported
Export terminated successfully without warnings.

任何 os 平台都適用的方法（推薦）：
以上兩種方法並不一定適合其他動作系統，不過使用 parfile 就不用擔心這些格式問題

oracle DBALNP01 > cat > test.par
tables=skytest
file=test.dmp
query="where dt=to_date('2007-09-22','yyyy-mm-dd')"

oracle DBALNP01 > exp / parfile=test.par

Export: Release 8.1.7.4.0 - Production on Wed Sep 12 04:22:27 2007

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

Connected to: Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
With the Partitioning option
JServer Release 8.1.7.4.0 - Production
Export done in US7ASCII character set and US7ASCII NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                        SKYTEST          1 rows exported
Export terminated successfully without warnings.

不需要任何轉義符既簡潔又可以在多種作業系統平台上通用，推薦使用這種方式。