数据库迁移——星空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://wenku.my7c.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
数据库迁移——星空oracle转sql server对象迁移完整性比较
1、oracle数据库统计表的数据量(oracle库执行)--DROP TABLE chl_tablerecordcreate table chl_tablerecord(ftablename varchar(300),fcou...
点击下载文档
本文2024-09-16 18:39:01发表“云星空知识”栏目。
本文链接:https://wenku.my7c.com/article/kingdee-k3cloud-23506.html
您需要登录后才可以发表评论, 登录登录 或者 注册
最新文档
热门文章