最近做的一个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;/