oracle 单独表的迁移,Oracle几种表迁移方法的介绍

在实际的生产环境中，一般是一个用户(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、迁移完成后及时检查相关约束情况，确保迁移前及迁移后数据的完整性和一致性。