需求场景:
业务系统环境升级,数据库表结构有变动,需要将原数据库中的所有数据导入到新的数据表结构中。
说到这里,大家脑海中可能一下会冒出很多方案,如:
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;
/