oracle import改表名

小技巧

现在工作有大量exp/imp工作，其中遇到过需要该表名的，恢复的时候也遇到过类似情况，之前都是把原表改名再让道给imp的表

现在有另一个方法可选择，思路如下：
1.复制现有表结构

2.在"另一用户"下简历一样的同义词，指向新表

3.使用"另一用户"imp

4.验收

5.drop同义词

实践：
table owner：asrs
原表：ttt2
新表：ttt2_new
另一用户：sys

新建立需要的原表：

conn asrs/password
SQL> create table ttt2 as select * from user_objects;

Table created.

SQL> select count(*) from ttt2;

  COUNT(*)
----------
      1971

简历新表，并复制表结构

SQL> create table ttt2_new as select * from ttt2 where rownum<1;

Table created.

SQL> select count(*) from ttt2_new;

  COUNT(*)
----------
         0

导出原表

[oracle@THWMSDB01 ~]$ exp asrs/password tables=ttt2 file=ttt2.dmp                         
Export: Release 10.2.0.3.0 - Production on Wed Mar 24 16:04:27 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
Export done in UTF8 character set and UTF8 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table                           TTT2       1971 rows exported

在另一用户下建立同义词：

[oracle@THWMSDB01 ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.3.0 - Production on Wed Mar 24 16:03:56 2010

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> CREATE SYNONYM TTT2 FOR ASRS.TTT2_NEW; 

Synonym created.

导入数据到同义词

[oracle@THWMSDB01 ~]$ imp \'sys/SINoracle10g as sysdba\' ignore=y file=ttt2.dmp commit=y full=y TOUSER=sys;   

Import: Release 10.2.0.3.0 - Production on Wed Mar 24 16:05:26 2010

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

Export file created by EXPORT:V10.02.01 via conventional path

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

import done in UTF8 character set and UTF8 NCHAR character set
. importing ASRS's objects into SYS
. . importing table                         "TTT2"       1971 rows imported
Import terminated successfully without warnings.

验收

[oracle@THWMSDB01 ~]$ sqlplus / as sysdba                                                
SQL*Plus: Release 10.2.0.3.0 - Production on Wed Mar 24 16:05:32 2010

Copyright (c) 1982, 2006, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

SQL> conn asrs/password;
Connected.
SQL> select count(*) from ttt2_new;

  COUNT(*)
----------
      1971

清理借用同义词

SQL> DROP SYNONYM TTT2;   

Synonym dropped.

SQL> conn asrs/asrs123;
Connected.
SQL> select count(*) from ttt2_new;

  COUNT(*)
----------
      1971

SQL> 

注意事项：
1.新表的表结构需要和dmp内的表结构一样

2."另一用户"需要有相应权限，这里使用了sys

3.导入时需要添加参数ignore=y避免重复建表

4.开始的时候建立了public的同义词失败，后来建立私有的就ok了