最近做的一个oracle数据迁移功能，如有更好的方案，请各位大虾也能告诉小弟。

需求场景：

业务系统环境升级，数据库表结构有变动，需要将原数据库中的所有数据导入到新的数据表结构中。

说到这里，大家脑海中可能一下会冒出很多方案，如：

1、使用exp和imp

2、使用dblink

 

针对第一种方案是行不通的，因为imp导入，必须保证用户下面是干净的，没有任何与dmp中重复的对象，不然就会导致数据无法正确导入。

针对第二种方案，dblink一般使用场景是在两台及以上服务器之间进行数据迁移，dblink它只为在两个服务器之间建立一个可互相访问的桥梁，最终还是需要进行insert into table select * from table。

 

以下是我自己实现的方案

思路：创建一个临时用户，刷入最新表结构，此时表中无任何数据，通过查询all_tables视图，查询出老用户的所有表对象，并通过查询dba_tab_columns视图，查询出每一个表的数据列，因为新表相对旧表有新增字段(注：这些新增字段是允许为null的，不然这种方案不一定好例)，再通过insert into table(columns) select columns from table语句将数据批量插入到新表中；同时为防止后续数据插入引起主键冲突，还需修改每个表对应序列的开始值，通过查询dba_sequences视图，将老用户中的所有序列对象名和last_number查询出来，再在新用户中通过删除老序列，创建新序列改变它的start with值即可。

 

步骤如下：

创建一个临时用户，刷入最新的表结构，并通过超管赋予如下权限，因为这些权限在做数据迁移时会用到

 

grant select on all_tables to TEMP_SDU;
grant select on dba_tab_columns to TEMP_SDU;
grant select on dba_sequences to TEMP_SDU;
grant connect,dba,resource to TEMP_SDU;
grant alter any sequence to TEMP_SDU;
grant create any sequence to TEMP_SDU;
grant execute any procedure to TEMP_SDU;
grant select any table to TEMP_SDU;

 

创建如下三个存储过程：

--获取表中的数据列，并进行批量插入

--table_name 表名
--temp_user 临时用户名
create or replace procedure p_getcolumns(table_name varchar2,temp_user varchar2)
as
  type ref_table_cursor is ref cursor;
  table_cursor ref_table_cursor;
  column_name varchar2(200);
  str_table_name varchar2(200);
  print_column varchar2(10000);
  str_sql varchar2(10000);
begin
    str_table_name := table_name;
 --Check out all the temporary user table column
    open table_cursor for select t.COLUMN_NAME from dba_tab_columns t where t.TABLE_NAME=str_table_name and t.OWNER=temp_user;
    loop
         fetch table_cursor into column_name;
         exit when table_cursor%notfound;
              --dbms_output.put_line(str_table_name||'--'||column_name);
              print_column := print_column||column_name||',';
    end loop;
    close table_cursor;
    print_column := substr(print_column,1,length(print_column)-1);
    str_sql := 'insert into '||table_name||'('||print_column||') select '||print_column||' from '||temp_user||'.'||table_name;
    --dbms_output.put_line(str_table_name||'--'||str_sql);
    execute immediate str_sql;
    commit;

    print_column := '';
    str_sql := '';
end p_getcolumns;
/

--修改序列开始值

--temp_user 临时用户名

create or replace procedure p_modify_sequences(temp_user varchar2)
as
    cursor cur_sequence is select sequence_name from dba_sequences where sequence_owner=temp_user and sequence_name not like '%S_BME%';
    seq_name varchar2(50);
    sequence_num number;
    num_count number;
begin
    open cur_sequence;
    loop
         fetch cur_sequence into seq_name;
         exit when cur_sequence%notfound;
              
              select count(*) into num_count from dba_sequences s where s.sequence_owner=temp_user and s.sequence_name=seq_name;
              
              if num_count>0 then
                 select s.last_number into sequence_num from dba_sequences s where s.sequence_owner=temp_user and s.sequence_name=seq_name;
                 select count(*) into num_count from dba_sequences s where s.sequence_owner='TEMP_SDU' and s.sequence_name=seq_name;
                 if num_count>0 then
                    execute immediate 'drop sequence '||seq_name;
                 end if;
                 execute immediate 'create sequence '||seq_name||' minvalue 1 maxvalue 999999999999 start with '||sequence_num||' increment by 1 nocache cycle';
                 
               end if;
              
    end loop;
    close cur_sequence;
    exception
    when no_data_found then
         sequence_num:=1;
         dbms_output.put_line(sqlerrm);
    when others then
         sequence_num:=1;
         dbms_output.put_line(sqlerrm);

end p_modify_sequences;
/

--数据迁移调用的入口存储过程
create or replace procedure p_datamove(temp_user varchar2)
as

  type ref_table_cursor is ref cursor;
  table_cursor ref_table_cursor;
  column_name varchar2(200);
  --All the tables in the query in temporary user
  cursor cur is select t.table_name from all_tables t where t.owner=temp_user and t.table_name not in('ECONF_ADMIN_INFO','ECONF_OPERATOR_PSWD_INFO','ECONF_EMAILSERVER','ECONF_HIREPORTS_INFO','ECONF_CONFIG','ECONF_EMAIL_FORMAT','ECONF_MS_FILE','ECONF_SVC_FEATURE_DATA','ECONF_IVR_VOICE_FILE','ECONF_IVR_VOICE_FILE_MCU','ECONF_SVC_DATA');
  str_sql varchar2(200);
  table_name varchar2(200);
  isexist number;

begin

  --savepoint p1;
  --Traverse each table name in the cursor
  open cur;
  loop
      fetch cur into table_name;
      exit when cur%NOTFOUND;
          select instr(table_name,'ECONF_') into isexist from dual;
          if isexist>0  then
                 --Get all the columns in the table, and the migration of data in each table
                 p_getcolumns(table_name,temp_user);
          end if;

  end loop;
  close cur;

  --modify sequences value
  p_modify_sequences(temp_user);

  exception
    when others then
         dbms_output.put_line(sqlerrm);
         --rollback to savepoint p1;

end p_datamove;
/