imp导入数据，能不能让导进来的表名跟原来导出的表名不同，也就是换个新的表名呢...

今天从网上看了一个问题

imp导入数据，能不能让导进来的表名跟原来导出的表名不同，也就是换个新的表名呢？

imp system/system file=mydmp.dmp fromuser=myuser1 touser=myuser2 tables=(mytable1)

这样能把dmp导出文件里面myuser1.mytable1导入到myuser2，但表名还是mytable1，能不能让导进来之后变为其他表名，比如mytable2？

请各位高手指点，谢谢！

[@more@]

借助synonym可以实现这个需求！

比如原来有个schema: user_a，在user_a下有个表test
先exp user_a的test表到dmp
然后在user_a下建个表test_new,create table test_new as select * from test_a where 1=0;
然后在另外一个用户(比如system)下建一个synonym
create synonym test for user_a.test_new;

接着把dmp文件导入到system下，自然就到了user_a.test_new了，最后把system下的synonym drop掉就OK了。

＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝

sqlplus fan/fan

SQL> create table fan_1 as select * from dba_objects;

Table created.

SQL> create table fan_2 as select * from fan_1 where rownum<1;

Table created.

exp fan/fan tables=fan_1 file=fan_1.dmp

sqlplus wrj/wrj

create or replace fan_1 for fan.fan_2;

select count(*) from fan_1;

0

$ imp wrj/wrj file=/oracle/fan_1.dmp commit=y ignore=y full=y

Import: Release 9.2.0.6.0 - Production on Thu Aug 28 15:59:56 2008

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

Warning: the objects were exported by FAN, not by you

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing FAN's objects into WRJ
. . importing table "FAN_1" 31964 rows imported
Import terminated successfully without warnings.
$
$ sqlplus fan/fan

SQL*Plus: Release 9.2.0.6.0 - Production on Thu Aug 28 16:01:14 2008

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> select count(*) from fan_2;

COUNT(*)
----------
31964

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/66233/viewspace-1009742/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/66233/viewspace-1009742/