[20180224]expdp query 寫法問題.txt

[20180224]expdp query 寫法問題.txt

--//如果使用expdp/impdp導入導出,如果參數複雜,最好的建議使用參數檔案.避免各種問題.通過簡單的例子說明問題.

1.環境:

SCOTT@book> @ &r/ver1

PORT_STRING                    VERSION        BANNER

------------------------------ -------------- --------------------------------------------------------------------------------

x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

2.測試:

--//假設僅僅導出表emp sal<=2000記錄:

$ expdp scott/book DUMPFILE=emp.dp  tables=emp query=emp:"where sal<2000"

Export: Release 11.2.0.4.0 - Production on Sat Feb 24 08:40:27 2018

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/a* DUMPFILE=emp.dp tables=emp query=emp:where sal<2000

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 64 KB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

ORA-31693: Table data object "SCOTT"."EMP" failed to load/unload and is being skipped due to error:

ORA-00936: missing expression

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:

  /u01/app/oracle/admin/book/dpdump/emp.dp

Job "SCOTT"."SYS_EXPORT_TABLE_01" completed with 1 error(s) at Sat Feb 24 08:40:36 2018 elapsed 0 00:00:09

--//注意看下線線,實際上導出報錯.

$ rm /u01/app/oracle/admin/book/dpdump/emp.dp

/bin/rm: remove regular file `/u01/app/oracle/admin/book/dpdump/emp.dp'? y

--//在where條件加入單引号.

$ expdp scott/book DUMPFILE=emp.dp  tables=emp query=emp:'"where sal<2000"'

Export: Release 11.2.0.4.0 - Production on Sat Feb 24 08:41:54 2018

Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/a*** DUMPFILE=emp.dp tables=emp query=emp:"where sal<2000"

. . exported "SCOTT"."EMP"                               8.320 KB       8 rows

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sat Feb 24 08:42:04 2018 elapsed 0 00:00:09

--//實際上對于linux bash要轉義"以及<.不轉義<,報錯.

$ expdp scott/book DUMPFILE=emp.dp  tables=emp query=emp:\"where sal<2000\"

-bash: 2000": No such file or directory

--//要寫成如下":

$ expdp scott/book DUMPFILE=emp.dp  tables=emp query=emp:\"where sal\<2000\"

Export: Release 11.2.0.4.0 - Production on Sat Feb 24 08:50:46 2018

Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/a** DUMPFILE=emp.dp tables=emp query=emp:"where sal<2000"

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sat Feb 24 08:50:56 2018 elapsed 0 00:00:09

--//OK成功.如果要導出 job='SALESMAN'的記錄更加麻煩.還要轉義裡面的單引号.

$ rm /u01/app/oracle/admin/book/dpdump/emp.dp

$ expdp scott/book DUMPFILE=emp.dp  tables=emp query=emp:\"where job=\'SALESMAN\'\"

Export: Release 11.2.0.4.0 - Production on Sat Feb 24 08:53:44 2018

Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/a*** DUMPFILE=emp.dp tables=emp query=emp:"where job='SALESMAN'"

. . exported "SCOTT"."EMP"                               8.171 KB       4 rows

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sat Feb 24 08:53:54 2018 elapsed 0 00:00:09

3.可以看出以上指令的複雜性,遇到這種情況最佳的方式建立使用參數檔案:

$ cat q.par

tables=emp

DUMPFILE=emp.dp

query=emp:"where sal<2000 and job='SALESMAN'"

$ expdp scott/book PARFILE=q.par

Export: Release 11.2.0.4.0 - Production on Sat Feb 24 09:01:37 2018

Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/a**** PARFILE=q.par

Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Sat Feb 24 09:01:48 2018 elapsed 0 00:00:09

總之:

遇到這種寫法特殊的expdp/impdp導入導出,最佳的方式就是使用參數檔案.

缺點就是不顯示參數檔案的内容,好像12c支援這些參數内容的顯示.