wulinggo

Oracle OCP+AIX+Linux+Weblogic 安装升级,容灾备份,故障分析,异常修复,数据恢复! 私We-Chat- 同博客号!

升级迁移前,存储过程统计各个用户下表的数据量,和迁移后的比对

  使用xtts升级数据库10.2.0.5到11.2.0.4后,比对两边数据量是否一致。

  首先,在源端新建一张表:

  

CREATE TABLE TMP_TAB_COUNT(owner VARCHAR2(30) ,TABLE_NAME VARCHAR2(50),TABLE_CNT_O INT) tablespace users;

  使用以下存储过程,查询用户ntcis下所有表的数据量:

begin
 for tb in (select table_name 
 from dba_tables 
 where owner='DSG'
 and TEMPORARY='N' 
) LOOP
 execute immediate 
 'insert into TMP_TAB_COUNT(owner,table_name,table_cnt_o)  select ''DSG'' as owner,'''
 ||tb.table_name
 ||''',(select count(*) from DSG.'
 ||'"'
 ||tb.table_name
 ||'"'
 ||') from dual';
 END LOOP;
 COMMIT;
END;
/

或者手动插入owner:

begin
 for tb in (select table_name 
 from dba_tables 
 where owner='NTCIS'
 and TEMPORARY='N' 
) LOOP
 execute immediate 
 'insert into TMP_TAB_COUNT1(table_name,table_cnt_o) select '''
 ||tb.table_name
 ||''',(select count(*) from NTCIS.'
 ||'"'
 ||tb.table_name
 ||'"'
 ||') from dual';
 END LOOP;
 COMMIT;
END;
/
UPDATE TMP_TAB_COUNT1 SET OWNER='NTCIS' WHERE OWNER IS NULL;
commit;

  同理,升级完成后,在目标端也进行相同的查询,再创建dblink使用minus可以查看到有异常的数据量:

select * from tmp_tab_count minus select * from tmp_tba_count1@desttns;

posted on 2019-05-24 09:36  wulinggo  阅读(277)  评论(0编辑  收藏  举报

导航