数据库迁移——星空oracle转sql server对象迁移完整性比较

1、oracle数据库统计表的数据量（oracle库执行）

--DROP TABLE chl_tablerecord
create table chl_tablerecord(ftablename varchar(300),fcount int,fcolumn_count int,fkeycount int);
insert into  chl_tablerecord(ftablename,fcolumn_count)
select a.table_name,b.fcount from user_tables a join (select table_name,count(1) fcount from user_tab_cols group by table_name) b on a.table_name=b.table_name
where a.table_name not  like 'TMP%' and a.table_name not like 'TM%'  and a.table_name not like 'MLOG%'    and a.table_name not like '%$%'  
and a.table_name not in ( select object_name from user_objects where object_type='MATERIALIZED VIEW'  );
COMMIT;
/
--统计数据量
declare 
var_ftablename varchar2(300);
cursor chl_cur is select  ftablename from chl_tablerecord;
begin
open chl_cur;
fetch chl_cur into var_ftablename;
while chl_cur%found loop  --使用游标属性
execute immediate 'update chl_tablerecord set fcount=(select count(1) from '||var_ftablename||') where ftablename='''||var_ftablename||'''' ;
fetch chl_cur into var_ftablename;
end loop;
close chl_cur;
end;
/
COMMIT;

2、在迁移后sql server数据库比较迁移对象完整性

注意下面用到的[ORC]为sql server到oracle的连接服务器，具体配置参考：

https://vip.kingdee.com/article/243415848155361536

---星空数据中心oracle数据库迁移为sql server数据库,如何比较迁移前和迁移后的库

----------------------------------------------------------------------------表对比
if exists(select 1 from sys.tables where name like 'chl_tablerecord')
drop table chl_tablerecord
create table chl_tablerecord(ftablename varchar(300),fcount int,fcolumn_count int,fkeycount int)
go
insert into chl_tablerecord(ftablename,fcolumn_count)
select name,b.fcolumn_count from sys.tables a join (select object_id,count(1)  fcolumn_count from sys.columns group by object_id) b  on a.object_id=b.object_id
where name not like 'z/_%' escape '/' and name not like 'tmp%'
update chl_tablerecord set fkeycount=0
update a set a.fkeycount=b.fcount from chl_tablerecord a join (select TABLE_NAME, CONSTRAINT_NAME,count(1) fcount from  INFORMATION_SCHEMA.KEY_COLUMN_USAGE group by TABLE_NAME, CONSTRAINT_NAME) b 
on a.ftablename=b.TABLE_NAME 
go
--查询oracle的表在迁移后的sql server库不存在
if exists(select 1 from sys.tables where name like 'chl_tablenotexists')
drop table chl_tablenotexists
select  * into chl_tablenotexists  from openquery([ORC],'select * from chl_tablerecord') 
where ftablename not in (select ftablename from  chl_tablerecord);
--请确认查询出来的表是否不需要的表
select * from chl_tablenotexists
----------------------------------------------------------------------------主键对比
if exists(select 1 from sys.tables where name like 'chl_primaryotexists')
drop table chl_primaryotexists
select  a.*,b.fkeycount fkeycount_mssql into chl_primaryotexists  from openquery([ORC],'select * from chl_tablerecord') a  join chl_tablerecord b on a.ftablename=b.ftablename
where a.fkeycount<>b.fkeycount
--查询oracle的主键在sql server不存在
select * from chl_primaryotexists

----------------------------------------------------------------------------列对比
if exists(select 1 from sys.tables where name like 'chl_tablecolnotexists')
drop table chl_tablecolnotexists
select t1.* into chl_tablecolnotexists  from  openquery([ORC],'select table_name,column_name from user_tab_cols where table_name in (select ftablename from chl_tablerecord  ) and column_name not like ''SYS_NC%''') t1
left join (select b.ftablename table_name,a.name column_name from  sys.columns  a join chl_tablerecord b on a.object_id=object_id(b.ftablename)  ) t2
on t1.table_name=t2.table_name and t1.column_name=t2.column_name
where t1.table_name not in (select ftablename from chl_tablenotexists) and t2.table_name is null
--查询oracle的列在迁移后的sql server 数据库不存在
select * from chl_tablecolnotexists
---------------------------------------------------------------------------数据量对比
--sql server统计表数据量
declare @ftablename varchar(300)
declare  chl_cur cursor for
select  ftablename from chl_tablerecord
open chl_cur
fetch chl_cur into @ftablename
while(@@FETCH_STATUS<>-1)
begin
exec('update chl_tablerecord set fcount=(select count(1) from '+@ftablename+') where ftablename='''+@ftablename+'''')
fetch chl_cur into @ftablename
end
close chl_cur
deallocate chl_cur
go
--收集数据量不同的表
if exists(select 1 from sys.tables where name like 'chl_tablerecordnotequal')
drop table chl_tablerecordnotequal
select  a.*,b.fcount  fcount_msql into chl_tablerecordnotequal  from openquery([ORC],'select ftablename,fcount from chl_tablerecord') a join chl_tablerecord b on a.ftablename=b.ftablename
where isnull(a.fcount,0)<>isnull(b.fcount,0)
--查询数据量不同的表
select *from  chl_tablerecordnotequal
---------------------------------------------------------------------------序列对比
if exists(select 1 from sys.tables where name like 'chl_sequencenotexits')
drop table chl_sequencenotexits
select  a.*  into chl_sequencenotexits from openquery([ORC],'select sequence_name from user_sequences where sequence_name like ''Z%''') a 
left join (select name sequence_name from sys.tables where name like 'z/_%'  escape '/') b on a.sequence_name=b.sequence_name
where b.sequence_name is null
--查询oracle的序列在迁移后sql server不存在
SELECT * FROM chl_sequencenotexits
---------------------------------------------------------------------------视图对比
if exists(select 1 from sys.tables where name like 'chl_viewnotexits')
drop table chl_viewnotexits
select  a.*  into chl_viewnotexits from openquery([ORC],'select * from user_views where view_name not like ''TMP%'' AND instr(view_name,''_'')>0 ') a 
left join sys.views b on a.view_name=b.name
where b.name is null
--查询oracle视图在迁移后sql server不存在
SELECT * FROM chl_viewnotexits

----------------------------------------------------------------------------索引比较
if exists(select 1 from sys.tables where name like 'indexnotexits')
drop table indexnotexits
select  a.*  into indexnotexits from openquery([ORC],'select table_name,index_name from user_indexes where index_name not like ''SYS_IL%''') a 
left join (select OBJECT_NAME(object_id) table_name,name  index_name from sys.indexes  )b on a.table_name=b.table_name and a.index_name=b.index_name
where a.table_name in (select ftablename from chl_tablerecord ) 
and b.table_name is null
--查询oracle索引在迁移后sql server不存在
SELECT * FROM indexnotexits
---------------------------------------------------------------------------存储过程和函数比较
if exists(select 1 from sys.tables where name like 'procedurenotexits')
drop table procedurenotexits
select  a.*  into procedurenotexits from openquery([ORC],'select  object_name fobject_name,object_type from user_objects where object_type in (''PROCEDURE'',''FUNCTION'') ') a 
left join (select name from sys.objects where type in ('P ','FN') )b on a.fobject_name=b.name
where b.name is null
--查询oracle存储过程和函数在迁移后sql server不存在
SELECT * FROM procedurenotexits