oracle+imp一个表,ORACLE IMP 字段不同的表

北京有张表wwm10,上海有张表wwm,

上海的wwm比北京的wwm10多一个valid字段,这个字段是default

1的,定期从北京导数据到上海.我采用的方法是建立一个基于上海wwm表的试图,试图的结构和北京wwm10

表相同,然后向这个试图导入数据,通过这个方法屏蔽表结构的不同.

SQL> select * from wwm; //目的表

NAME ID VALID

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

222 222 1

dddd 2222 1

ljjjjj 222 1

llll 1111 1

test 234

SQL> alter table wwm modify (valid default

1); //目的表比源表多一个VALID字段

Table altered.

SQL> insert into wwm(name,id) values

('qwer',456);

1 row created.

SQL> select * from wwm;

NAME ID VALID

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

222 222 1

dddd 2222 1

ljjjjj 222 1

llll 1111 1

test 234

qwer 456 1

6 rows selected.

SQL> create table wwm10 (town varchar2(10),postcode

number(6)); //源表

Table created.

SQL> insert into wwm10 values

('shanghai',00001);

1 row created.

SQL> insert into wwm10 values

('beijin',010010);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from

wwm10;

//源表有两条记录

TOWN POSTCODE

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

shanghai 1

beijin 10010

SQL> exit

导出数据Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 -

64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production

orasbt@svodbt01:$

exp user/pass tables=wwm10 file=wwm10;

Export: Release 9.2.0.6.0 - Production on Wed Aug 2 11:00:02

2006

Copyright (c) 1982, 2002, Oracle Corporation. All

rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit

Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character

set

server uses UTF8 character set (possible charset

conversion)

About to export specified tables via Conventional Path ...

. . exporting

table WWM10 2 rows exported

Export terminated successfully without warnings.

orasbt@svodbt01:$

sqlplus user/pass

SQL*Plus: Release 9.2.0.6.0 - Production on Wed Aug 2 11:00:18

2006

Copyright (c) 1982, 2002, Oracle Corporation. All

rights reserved.

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit

Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production

SQL> drop table

wwm10;

//因为我是在同一个库里做实验，为了描述简单点就先删掉这个表

Table dropped.

SQL> create view wwm10 as select name town,id

postcode from

wwm;

//建立与源表字段相同的试图。

View created.

SQL> select * from

wwm10;

TOWN POSTCODE

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

222 222

dddd 2222

ljjjjj 222

llll 1111

test 234

qwer 456

6 rows selected.

SQL> exit

导入数据Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 -

64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production

orasbt@svodbt01:$

imp user/pass file=wwm10 ignore=y;

Import: Release 9.2.0.6.0 - Production on Wed Aug 2 11:01:24

2006

Copyright (c) 1982, 2002, Oracle Corporation. All

rights reserved.

Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - 64bit

Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.6.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path

import done in ZHS16GBK character set and AL16UTF16 NCHAR character

set

import server uses UTF8 character set (possible charset

conversion)

. importing SBTOPT's objects into SBTOPT

. . importing

table "WWM10" 2 rows imported

Import terminated successfully without warnings.

SQL> select * from wwm;

NAME ID VALID

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

222 222 1

dddd 2222 1

ljjjjj 222 1

llll 1111 1

test 234

qwer 456 1

shanghai 1 1

beijin