今天从网上看了一个问题
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