SQL> conn yang/yang as sysdba
已連線。
SQL> select table_name from dba_tables where table_name like 'T_%';---在這裡 _ 被當作萬用字元來處理。

TABLE_NAME
------------------------------
TAB$
TYPE_MISC$
TS$
TSQ$
TYPED_VIEW$
TRUSTED_LIST$
TRIGGER$
TRIGGERCOL$
TRIGGERJAVAF$
TRIGGERJAVAS$
TRIGGERJAVAC$

TABLE_NAME
------------------------------
TRIGGERJAVAM$
TRIGGERDEP$
TSM_SRC$
TSM_DST$
TABLE_PRIVILEGE_MAP
TAB_STATS$
TYPE$
TYPEHIERARCHY$
TABPART$
TABSUBPART$
TABCOMPART$

TABLE_NAME
------------------------------
TRANSACTION_BACKOUT_STATE$
TRANSACTION_BACKOUT_REPORT$
TRANSFORMATIONS$
TOKENSESSIONMAP_T$
T1
T2
TTS_TBS$
TTS_USR$
TTS_ERROR$
已選擇31行。
SQL> select table_name from dba_tables where table_name like 'TTS\_%' escape '\';--加上escape後，_ 當作下劃線來處理
TABLE_NAME
------------------------------
TTS_ERROR$
TTS_USR$
TTS_TBS$

當需要匯出某一類表比如以 test_ 開頭的表時 escape 就派上用場了，匯出TEST_1 TEST_2 這兩個表。

SQL> conn scott/tiger
Connected.

SQL> select * from tab;

TNAME                         TABTYPE  CLUSTERID
------------------------------ ------- ----------
DEPT                         TABLE
EMP                         TABLE
BONUS                         TABLE
SALGRADE                      TABLE
TEST_1                      TABLE
TEST_2                      TABLE
TESTA                         TABLE
TESTB                         TABLE
SQL> select tname from tab where tname like 'TEST\_%' escape '\';

TNAME
------------------------------
TEST_1
TEST_2
SQL> conn / as sysdba
SQL> create or replace directory dumpdir as  'd:\dump';
SQL> grant read,write on directory dumpdir to scott;

C:\Documents and Settings\Administrator>expdp scott/tiger DIRECTORY=dumpdir DUMPFILE=expdp_scott_test.dmp LOGFILE=expdp_scott_test.log SCHEMAS=scott INCLUDE=TABLE:\"IN $ select tname from tab where tname like 'TESTq_%' escape 'q' $\"

Export: Release 10.1.0.2.0 - Production on 星期五, 22 10月, 2010 12:45

Copyright (c) 2003, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SCOTT"."SYS_EXPORT_SCHEMA_01":  scott/******** DIRECTORY=dumpdir DUMPFILE=exp_tab.dmp LOGFILE=exp_tab.log SCHEMAS=scott INCLUDE=TABLE:"IN $ select tn
ame from tab where tname like 'TESTq_%' escape 'q' $"
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 128 KB
Processing object type SCHEMA_EXPORT/TABLE/TABLE
. . exported "SCOTT"."TEST_1"                         7.820 KB    14 rows
. . exported "SCOTT"."TEST_2"                         7.820 KB    14 rows
Master table "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_SCHEMA_01 is:
  D:\DUMP\EXP_TAB.DMP
Job "SCOTT"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:45
---匯出部分的例項來自與puber stanley ！

【oracle 匯入、匯出】escape 的作用。

相關文章