Oracle 数据迁移细节

我有一个从oracle源模式到目标oracle模式的数据迁移任务；
同时考虑以下几点：

*源/目标模式的设计相同。

*在源架构中；有：

# SourceTable：包含一些数据的表（由oracle序列自动生成的主键）
# RefSourceTable：对SourceTable具有FK引用的表

*在目标架构中：有

# TargetTable：包含一些数据的表（由oracle序列自动生成的主键）
# RefTargetTable：对TargetTable具有FK引用的表

该任务包括迁移数据：

1.从SourceTable到TargetTable
2.从RefSourceTable到RefTargetTable

但是，我无法迁移主键
因为DestinationTable可能有其自己的记录，并且具有相同的主键
这可能会造成PK违规。

这意味着DestionationTable中的新记录将具有新的ID。

我遇到的问题是：

1.如何维护FK引用
将RefSourceTable迁移到参考目标表时？

2、如何维护oracle顺序（用于自动生成主键TargetTable ）
在目标架构中迁移数据之后？

Oracle是否有足够的迁移工具来支持数据迁移的特定性？

选择一些合适的范围如何？例如

SQL> drop table SOURCE purge;

Table dropped.

SQL> drop table TARGET purge;

Table dropped.

SQL>
SQL> drop sequence SEQ_SOURCE ;

Sequence dropped.

SQL> drop sequence SEQ_TARGET ;

Sequence dropped.

SQL>
SQL> create table SOURCE ( x int primary key, d varchar2(10));

Table created.

SQL> create table TARGET ( x int primary key, d varchar2(10));

Table created.

SQL>
SQL> create sequence seq_SOURCE;

Sequence created.

SQL> create sequence seq_TARGET start with 20;

Sequence created.

SQL>
SQL> insert into SOURCE
  2  select SEQ_SOURCE.nextval, 'source' from dual
  3  connect by level <= 100;

100 rows created.

SQL>
SQL> insert into TARGET
  2  select SEQ_TARGET.nextval, 'target' from dual
  3  connect by level <= 100;

100 rows created.

SQL>
SQL> select min(x), max(x) from source;

    MIN(X)     MAX(X)
---------- ----------
         1        100

SQL> select min(x), max(x) from target;

    MIN(X)     MAX(X)
---------- ----------
        20        119

SQL>
SQL> insert into TARGET
  2  select * from SOURCE;
insert into TARGET
*
ERROR at line 1:
ORA-00001: unique constraint (MCDONAC.SYS_C0015059) violated

这里我们已经演示了碰撞...现在我们要做一些序列的摆弄来避免它

--
-- find current high mark
--
SQL>
SQL> variable max_target number
SQL> exec select max(t.x) into :max_target from target t

PL/SQL procedure successfully completed.

--
-- insert above that mark, please leaving 100 for other insertions 
-- to TARGET to take place whilst we do that.  (You can pick any
-- appropriate value here
--  
SQL> insert into TARGET
  2  select
  3     :max_target + 100 + x, d
  4  from SOURCE;

100 rows created.

--
-- now remember the new high mark
--
SQL>
SQL>
SQL> variable new_max_target number
SQL> exec select max(t.x) into :new_max_target from target t

PL/SQL procedure successfully completed.

--
-- and adjust the sequence by that much
--

SQL>
SQL> declare
  2    res int;
  3  begin
  4    execute immediate
  5      'alter sequence seq_TARGET increment by '||(:new_max_target-:max_target);
  6    select seq_TARGET.nextval into res from dual;
  7    execute immediate
  8      'alter sequence seq_TARGET increment by 1';
  9  end;
 10  /

PL/SQL procedure successfully completed.

--
-- and now our TARGET and target sequence are good to go
--

SQL>
SQL> select max(x) from target;

    MAX(X)
----------
       319

SQL> select seq_TARGET.nextval from dual;

   NEXTVAL
----------
       320

SQL>
SQL>

只要您知道（或记录）更改源主键序列的增量，那么您只需将该增量添加到参照完整性键等中。