oracle导入导出exp不同表名,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

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

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值