返回首页
当前位置: 主页 > 数据库 > Oracle教程 >

Oracle几种表迁移方法的介绍

时间:2016-09-28 23:51来源:电脑教程学习网 www.etwiki.cn 编辑:admin

在实际的生产环境中,一般是一个用户(schema)对应一个表空间,该用户相应的表都建议在这个默认的表空间里面。但个别情况下,例如初期规划的调整等,需要将用户的表迁移到另一个用户下,这时就有有多种可行的实施方法,本文介绍几种比较高见的做法。
 
【正文】

一     用户表迁移的目的

数据库运维过程中,可能会遇到应用系统调整或进行数据库的总体整合,一般的目的有以下几个:用户将几个小的实例整合到成一个;没有指定默认表空间,初期人为误操作将用户表建到其它表空间里;应用系统或业务需求的架构调整。这里主要分两种情况:
  相同schema下迁移表到其它表空间。
  不同schema下的表迁移。
 

二      常见的表迁移方法

2.1   导入导出法

场景:主要适用于不同schema下的表迁移。
示例:将跨用户的表迁移。将scott用户的表迁移到orcl用户下,可以通过exp/imp工具进行导出导入,也可以通过expdp/impdp工具来完成。
通过exp/imp工具,(导入时使用fromuser… touser…命令来实现用户转换):
导出:exp scott/password@orcl file=/home/oracle/emp.dump log=emp.log tables=emp
导入:imp orcl/password@orcl fromuser=scott touser=orcl file=/home/oracle/emp.dump log=emp.log tables=emp;
通过expdp/impdp工具,(导入时使用remap_schema命令来实现)
导出:expdp system/password@orcl directory=dir_dp dumpfile=emp.dmp log=emp.log tables=scott.emp
导入:impdp system/password@orcl directory=dir_dp dumpfile=emp.dmp log=emp.log tables=scott.emp remap_schema=scott:orcl
       此时如果原schema下的表已不再使用,可直接用drop table命令删除。
通过上述导入导出方式来迁移表的方法需要注意的地方是,表的约束条件会伴随着dump文件一起走,如果与导入的表有约束相关的其它表不存在,则会报错如下:
IMP-00017: following statement failed with ORACLE error 942:
 "ALTER TABLE "EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFEREN" "CES "DEPT" ("DEPTNO") ENABLE NOVALIDATE"
或者
ORA-00942: table or view does not exist
Failing sql is:
ALTER TABLE "ORCL"."EMP" ADD CONSTRAINT "FK_DEPTNO" FOREIGN KEY ("DEPTNO") REFERENCES "ORCL"."DEPT" ("DEPTNO") ENABLE
 

2.2   移动法

另外一种迁移表的方法是使用alter table table_name move tablespace tablespace_name命令将表移动到另一个表空间(用户也转变),主要适用于相同schema下将表迁移到其它表空间,示例:将emp表从users表空间转移到orcl表空间:
SQL> alter table emp move tablespace orcl;
 
此时只是表的位置转移了,用户不变:
SQL> select owner,table_name,tablespace_name from dba_tables where table_name='EMP';
OWNER     TABLE_NAME    TABLESPACE
----------               ----------                      ----------
SCOTT      EMP             ORCL
 
注意:此时表的相关索引仍在users表空间,只是已经变为无效状态:
SQL> select INDEX_NAME,TABLE_NAME,STATUS from user_indexes where table_name='EMP';
INDEX_NAME    TABLE_NAME   STATUS
-------------------    ----------------            --------
PK_EMP         EMP             UNUSABLE
      
需要重建索引到orcl表空间:
       SQL> alter index PK_EMP rebuild online tablespace orcl;
 

2.3  重建法

该方法比较简单,即选取所要表重建到其它地方,适用于不同schema之间的表迁移,面对普通表、小表一般都可以使用这种方法,需要注意的地方是权限问题,即示例:
首先使用sysdba账户赋予orcl用户对表scott.emp的读取权限:
SQL> grant select on scott.emp to orcl;
       切换到orcl用户下:
SQL> conn orcl/password
SQL> create table emp as select * from scott.emp;
 
注意:1、使用该方法只是创建表的结构,而不会将原表的默认值一起创建。
          2、只会复制表数据和表结构,不会有任何约束。
          3、当后面 where 条件不成立时,只复制表结构,没有任务数据。
 

2.4  复制法

如果表里包含了long字段,使用移动法或重建法则会报错“ORA-00997: 非法使用 LONG 数据类型”,原因是long字段不能使用move命令或create命令,需要使用copy命令来进行表迁移:
SQL> copy from  scott/password@orcl to orcl/password@orcl replace emp using select * from emp;
 
Array fetch/bind size is 15. (arraysize is 15)
Will commit when done. (copycommit is 0)
Maximum long size is 80. (long is 80)
Table EMP created.
 
     14 rows selected from scott@orcl.
     14 rows inserted into EMP.
     14 rows committed into EMP at orcl@orcl.
 
FROM或TO指向当前数据库时,可以忽略,但不能同时忽略FROM和TO。
       与move命令不同,copy命令是以复制的方式,上述操作最后的效果是scott用户和orcl用户下都有一个名为emp的表,可见copy方式与导入导出相似,适用于不同用户下表schema的迁移,如果想达成直接移动法的效果,在不改变schema的情况下仅仅实现表空间的转移,则可以考虑下述方法:
       先修改scott用户的默认表空间为orcl:
       SQL> alter user scott default tablespace orcl;
       然后重建emp表到相同的schema下:
SQL> copy from  scott/password@orcl to scott/password@orcl replace emp using select * from emp;
       新建的表就会生成在默认的表空间下,而replace命令则会删除原users表空间下的emp表。最后再将默认表空间修改回来即可,实际生产环境中除非需要修改默认表空间,否则不推荐该做法。
使用复制法有一个缺点,那就是针对表的复制并不包括其约束条件,需要在复制完成后手动重建创建约束。

三    操作总结

1、表数据迁移的方法有很多,需要结合具体的实际情况来选择最佳方法。
2、移动法适用于相同schema下表空间之间的数据迁移,如果需要变更schema的话,建议使用另外三种方法。
3、在表移动期间,该表无法访问,需要相关业务即应用暂停直至迁移完成。
4、任何数据迁移操作前都需要做好完善的备份和测试工作。
5、迁移完成后及时检查相关约束情况,确保迁移前及迁移后数据的完整性和一致性。
------分隔线----------------------------
标签(Tag):Oracle oracle数据库 数据库
------分隔线----------------------------
推荐内容
猜你感兴趣