Db2迁移到oracle的思路

准备工具：db2tooracle,sqldeveloper,dbvisualizer,textdiff

准备脚本：导sequence,查看表记录，收集统计信息

通过db2执行RUNSTATS ON生成最新统计信息，统计每个表的记录数
通过db2tooracle导表快速导表到oracle（试用版每次20个表+不超过1000条记录的表）
通过sqldeveloper导1中大于1000条记录和报错未成功的表
通过dbvisualizer导出所有对象，包括视图、索引、约束外键和触发器等
应用开发验证测试，注意字段类型变化
根据问题排查处理
对比记录数，正式迁移只导变化的表即可，迁移考虑并行。

使用导的脚本参考：

Db2迁移到oracle的思路

select tabname,card from syscat.tables where tabschema = '用户名' and type='T' order by tabname asc;

--oracle下查询

create or replace function count_rows(table_name in varchar2,

owner in varchar2 default null)

return number authid current_user IS

num_rows number;

stmt varchar2(2000);

begin

if owner is null then

stmt := 'select count(*) from "' || table_name || '"';

else

stmt := 'select count(*) from "' || owner || '"."' || table_name || '"';

end if;

execute immediate stmt

into num_rows;

return num_rows;

end;

执行：select table_name, count_rows(table_name) nrows from user_tables;

select tabname,card from syscat.tables where tabschema = '用户名' and type='T' and card>=1000 order by tabname asc;

select 'CREATE SEQUENCE ' ||SEQNAME||

' MINVALUE ' ||MINVALUE||

' MAXVALUE ' ||MAXVALUE||

( case when LASTASSIGNEDVAL IS NULL then ' START WITH '||CHAR(BIGINT(START)+1) else ' START WITH '||CHAR(BIGINT(LASTASSIGNEDVAL)+1) end)||

' INCREMENT BY ' ||INCREMENT||

( case when CYCLE='N' then ' NOCYCLE ' else ' CYCLE ' end)||

( case when CACHE=0 then ' NOCACHE ' else ' CACHE '|| CACHE end)||

( case when ORDER='N' then ' NOORDER ' else ' ORDER ' end)||

';'

from SYSIBM.SYSSEQUENCES where OWNER= '用户名';

导出外键并修改替换ON update CASCADE对应DEFERRABLE;

select TABNAME,COLNAME ,LENGTH from syscat.columns where tabschema='用户名' AND LENGTH>2000 and LENGTH<10000;

select TABLE_NAME,column_name,DATA_TYPE,data_length
from user_tab_columns
where TABLE_NAME in('AOP_LOG'
,'BATCH_JOB_EXECUTION'
,'BATCH_JOB_EXECUTION_CONTEXT'
,'BATCH_STEP_EXECUTION'
,'BATCH_STEP_EXECUTION_CONTEXT'
,'ETL_RECORD'
,'PSI_BAS_DRUG'
,'PSI_BAS_DRUG_SPLITPACKING'
,'PSI_OUTP_INJURY_BASE'
,'PSI_OUTP_RCPT_INJURY'
,'PSI_OUTP_REGIST_INJURY'
,'YX_DRUG') and column_name in('CONTENT'
,'SHORT_CONTEXT'
,'EXIT_MESSAGE'
,'REMARK'
,'INJURY_PROCES'
,'AMOUNT')
order by TABLE_NAME,column_name;

修改字段类型：

--alter table 表名 modify CHECK_RESULT varchar2(3000) ;

alter table 表名 rename column CHECK_RESULT TO CHECK_RESULT_BK ;

alter table 表名 add CHECK_RESULT VARCHAR2(3000) ;

update 表名 set CHECK_RESULT = dbms_lob.substr( CHECK_RESULT_BK ,3000) ;

commit;

alter table 表名 drop column CHECK_RESULT_BK ;

Db2迁移到oracle的思路

相关推荐