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

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$                                                                  

triggerjavam$                                                                  

triggerdep$                                                                    

tsm_src$                                                                       

tsm_dst$                                                                       

table_privilege_map                                                            

tab_stats$                                                                     

type$                                                                          

typehierarchy$                                                                 

tabpart$                                                                       

tabsubpart$                                                                    

tabcompart$                                                                    

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後，_ 當作下劃線來處理

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 ！