1.DM存储过程示例子，表的行数对比
1 --1. ================全量 2 --select * from DM_VERI where c2 != c3; 3 --drop table DM_VERI; 4 --create table DM_VERI(c1 varchar(100) PRIMARY KEY, c2 int, c3 int); 5 --truncate table DM_VERI; 6 7 --1.1 8 CREATE OR REPLACE PROCEDURE proc_veri(v_user IN VARCHAR2) 9 AS 10 v_table varchar(40); 11 v_count number; 12 v_count2 number; 13 v_sql VARCHAR(1024); 14 v_sql2 VARCHAR(1024); 15 c1 CURSOR; 16 BEGIN 17 OPEN c1 FOR SELECT TABLE_NAME FROM dba_tables t where t.OWNER=v_user; 18 LOOP 19 FETCH c1 INTO v_table; 20 21 --1.dm 22 v_sql:='select COUNT(*) from' || v_table; 23 execute immediate v_sql into v_count; 24 25 --2.ora 26 --v_sql2:='select COUNT(*) from' || v_table||'@link01'; 27 v_sql2:='select COUNT(*) from' || v_table; 28 execute immediate v_sql2 into v_count2; 29 30 31 insert into DM_VERI values (v_table, v_count, v_count2); 32 33 PRINT v_table || v_count||v_count2; 34 --PRINT v_count; 35 36 EXIT WHEN c1%NOTFOUND; 37 38 END LOOP; 39 CLOSE c1; 40 END; 41 42 --1.2 43 --call proc_veri('SYSDBA'); 44 45 46 --====================================================================== 47 --2. 分布sql 48 --2.1 ora 49 --select * from TEST_V_ORA where c2 != c3; 50 --drop table TEST_V_ORA; 51 --create table TEST_V_ORA(c1 varchar(100), c2 int); 52 --truncate table TEST_V_ORA; 53 CREATE OR REPLACE PROCEDURE proc_v_ora(v_user IN VARCHAR) 54 AS 55 v_table varchar(40); 56 v_count2 number; 57 v_sql2 VARCHAR(1024); 58 c1 CURSOR; 59 BEGIN 60 OPEN c1 FOR SELECT TABLE_NAME FROM dba_tables t where t.OWNER=v_user; 61 LOOP 62 FETCH c1 INTO v_table; 63 64 --2.ora 65 --v_sql2:='select COUNT(*) from' || v_table||'@link01'; 66 v_sql2:='select COUNT(*) from' || v_table; 67 execute immediate v_sql2 into v_count2; 68 69 70 insert into TEST_V_ORA(c1,c3) values (v_table, v_count2); 71 72 PRINT v_table || v_count2; 73 74 EXIT WHEN c1%NOTFOUND; 75 76 END LOOP; 77 CLOSE c1; 78 commit; 79 END; 80 81 truncate table DM_VERI; 82 83 call proc_veri_ora('SYSDBA'); 84 85 select * from DM_VERI; 86 87 88 --2.2 dm 89 --select * from TEST_V_DM where c2 != c3; 90 --drop table TEST_V_DM; 91 --create table TEST_V_DM(c1 varchar(100), c2 int); 92 --truncate table TEST_V_DM; 93 CREATE OR REPLACE PROCEDURE proc_v_dm(v_user IN VARCHAR) 94 AS 95 v_table varchar(40); 96 v_count number; 97 v_sql VARCHAR(1024); 98 c1 CURSOR; 99 BEGIN 100 OPEN c1 FOR SELECT TABLE_NAME FROM dba_tables t where t.OWNER=v_user; 101 LOOP 102 FETCH c1 INTO v_table; 103 104 --1.dm 105 v_sql:='select COUNT(*) from' || v_table; 106 execute immediate v_sql into v_count; 107 108 109 update TEST_V_DM set c2=v_count where c1=v_table; 110 111 PRINT v_table || v_count; 112 113 EXIT WHEN c1%NOTFOUND; 114 115 END LOOP; 116 CLOSE c1; 117 commit; 118 END; 119 120 121 call proc_v_dm('SYSDBA'); 122 123 select * from TEST_V_DM; 124 125 126 ------==========================ORA 数据对比============================== 127 128 select 'select count(*) cnt,'''||TABLE_NAME||'''from ' || TABLE_NAME||' union all ' from ( 129 select TABLE_NAME from DBA_TABLES 130 where OWNER like 'SYSDBA' AND TABLE_NAME NOT LIKE '##%' order by TABLE_NAME);