linux下exp导出hcp_开头的表的困惑

显示全部楼层 · 2010-10-8 09:31:22

我在linux系统上导出特定表
我只想导出hcp_开头的表，这样的表非常多，所以不便用列表，可我下面的写法却把HCPSDF_1这种表也导出来了，相当于把hcp开头的导出了，那个'_'没起到作用，请帮忙看看到底应该怎么写？
rpt@oracle:/opt/oracle/hcp>exp \'/ as sysdba\' tables=hcp_% file=hcp.dmp log=hcp.log
Export: Release 9.2.0.4.0 - Production on Mon Oct 18 11:18:29 2010
Copyright (c) 1982, 2002, Oracle Corporation.All rights reserved.

Connected to: Oracle9i Release 9.2.0.4.0 - Production
JServer Release 9.2.0.4.0 - Production
Export done in ZHS16CGB231280 character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table
HCPSDF_1
0 rows exported
. . exporting table
HCP_1
0 rows exported
. . exporting table
HCP_TEST
2 rows exported
Export terminated successfully without warnings.
[ 本帖最后由浪漫双鱼于 2010-10-18 11:24 编辑 ]

千问 · 2010-10-8 09:31:22

_%这两个都是通配符，看你前面也用了转义的，尝试加一下吧

千问 · 2010-10-8 09:31:22

试过，也不行
rpt@oracle:/opt/oracle/hcp>exp \'/ as sysdba\' tables=hcp\_% file=hcp.dmp log=hcp.log
Export: Release 9.2.0.4.0 - Production on Mon Oct 18 12:08:05 2010
Copyright (c) 1982, 2002, Oracle Corporation.All rights reserved.

Connected to: Oracle9i Release 9.2.0.4.0 - Production
JServer Release 9.2.0.4.0 - Production
Export done in ZHS16CGB231280 character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table
HCPSDF_1
0 rows exported
. . exporting table
HCP_1
0 rows exported
. . exporting table
HCP_TEST
2 rows exported
Export terminated successfully without warnings.

千问 · 2010-10-8 09:31:22

hcp\_% escape '\'
-------------------------------------------------------------------------------------------------
SQL> select table_name from dba_tables where table_name like 'T_%';
TABLE_NAME
------------------------------
TSQ$
TS$
TAB$
TYPE_MISC$
TYPED_VIEW$
TRUSTED_LIST$
TRIGGER$
TRIGGERCOL$
TRIGGERJAVAF$
TRIGGERJAVAS$
TRIGGERJAVAC$
TABLE_NAME
------------------------------
TRIGGERJAVAM$
TRIGGERDEP$
TRANSIENT_IOT$
TSM_SRC$
TSM_DST$
TABLE_PRIVILEGE_MAP
TAB_STATS$
TYPE$
TYPEHIERARCHY$
TABPART$
TABSUBPART$
TABLE_NAME
------------------------------
TABCOMPART$
TRANSACTION_BACKOUT_STATE$
TRANSACTION_BACKOUT_REPORT$
TRANSFORMATIONS$
TEST
TTS_TBS$
TTS_USR$
TTS_ERROR$
TOKENSESSIONMAP_T$
已选择31行。

SQL> select table_name from dba_tables where table_name like 'T\_%' escape '\';
未选定行
SQL> select table_name from dba_tables where table_name like 'TTS\_%' escape '\';
TABLE_NAME
------------------------------
TTS_USR$
TTS_TBS$
TTS_ERROR$
SQL>
[ 本帖最后由 lxq1985 于 2010-10-18 12:47 编辑 ]

千问 · 2010-10-8 09:31:22

/opt/oracle/hcp>exp \'/ as sysdba\' tables=hcp__% file=hcp.dmp log=hcp.log
注意上面的hcp__% 是两个下划线

千问 · 2010-10-8 09:31:22

/opt/oracle/hcp>exp \'/ as sysdba\' tables=hcp__% file=hcp.dmp log=hcp.log
注意上面的hcp__% 是两个下划线

千问 · 2010-10-8 09:31:22

原帖由 ilsyx 于 2010-10-18 13:33 发表
/opt/oracle/hcp>exp \'/ as sysdba\' tables=hcp__% file=hcp.dmp log=hcp.log
注意上面的hcp__% 是两个下划线
两个下划线和一个下划线导出效果都是下面这样

About to export specified tables via Conventional Path ...
. . exporting table
HCPSDF_1
0 rows exported
. . exporting table
HCP_1
0 rows exported
. . exporting table
HCP_TEST
2 rows exported
Export terminated successfully without warnings.

千问 · 2010-10-8 09:31:22

原帖由 lxq1985 于 2010-10-18 12:45 发表
hcp\_% escape '\'
-------------------------------------------------------------------------------------------------
SQL> select table_name from dba_tables where table_name like 'T_%';
TABLE_NAME
------------------------------
TSQ$
TS$
TAB$
TYPE_MISC$
TYPED_VIEW$
TRUSTED_LIST$
TRIGGER$
TRIGGERCOL$
TRIGGERJAVAF$
TRIGGERJAVAS$
TRIGGERJAVAC$
TABLE_NAME
------------------------------
TRIGGERJAVAM$
TRIGGERDEP$
TRANSIENT_IOT$
TSM_SRC$
TSM_DST$
TABLE_PRIVILEGE_MAP
TAB_STATS$
TYPE$
TYPEHIERARCHY$
TABPART$
TABSUBPART$
TABLE_NAME
------------------------------
TABCOMPART$
TRANSACTION_BACKOUT_STATE$
TRANSACTION_BACKOUT_REPORT$
TRANSFORMATIONS$
TEST
TTS_TBS$
TTS_USR$
TTS_ERROR$
TOKENSESSIONMAP_T$
已选择31行。

SQL> select table_name from dba_tables where table_name like 'T\_%' escape '\';
未选定行
SQL> select table_name from dba_tables where table_name like 'TTS\_%' escape '\';
TABLE_NAME
------------------------------
TTS_USR$
TTS_TBS$
TTS_ERROR$
SQL>

rpt@oracle:/opt/oracle/hcp>exp \'/ as sysdba\' tables=hcp\_% escape '\' file=hcp.dmp log=hcp.log
Export: Release 9.2.0.4.0 - Production on Mon Oct 18 14:05:23 2010
Copyright (c) 1982, 2002, Oracle Corporation.All rights reserved.

Connected to: Oracle9i Release 9.2.0.4.0 - Production
JServer Release 9.2.0.4.0 - Production
Export done in ZHS16CGB231280 character set and AL16UTF16 NCHAR character set
server uses ZHS16GBK character set (possible charset conversion)
About to export specified tables via Conventional Path ...
. . exporting table
HCPSDF_1
0 rows exported
. . exporting table
HCP_1
0 rows exported
. . exporting table
HCP_TEST
2 rows exported
EXP-00011: SYS.ESCAPE does not exist
Export terminated successfully with warnings.
还是不行，exp中没得escape这个参数得
[ 本帖最后由浪漫双鱼于 2010-10-18 14:10 编辑 ]

千问 · 2010-10-8 09:31:22

使用expdp就可以了，我是在XP下测试的 db 10g
SQL> conn scott/tiger
Connected.
SQL> select * from tab;
TNAME
TABTYPECLUSTERID
------------------------------ ------- ----------
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 KB14 rows
. . exported "SCOTT"."TEST_2"
7.820 KB14 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
希望以上对你有所帮助，

千问 · 2010-10-8 09:31:22

TO LS
他是9i的...