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/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值