小技巧
现在工作有大量exp/imp工作,其中遇到过需要该表名的,恢复的时候也遇到过类似情况,之前都是把原表改名再让道给imp的表
现在有另一个方法可选择,思路如下:
1.复制现有表结构
2.在"另一用户"下简历一样的同义词,指向新表
3.使用"另一用户"imp
4.验收
5.drop同义词
实践:
table owner:asrs
原表:ttt2
新表:ttt2_new
另一用户:sys
新建立需要的原表:
简历新表,并复制表结构
导出原表
在另一用户下建立同义词:
导入数据到同义词
验收
清理借用同义词
注意事项:
1.新表的表结构需要和dmp内的表结构一样
2."另一用户"需要有相应权限,这里使用了sys
3.导入时需要添加参数ignore=y避免重复建表
4.开始的时候建立了public的同义词失败,后来建立私有的就ok了
现在工作有大量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了