oracle对比两张表的行数,比较两个Oracle数据库中表的数量和行数

使用Goldengate做迁移时，有时需要验证源和目标数据库中某Schema中的表的数量和行数是否一致。

可以使用以下简单的方法：

-- t.sql

set feedback off

set serveroutput on

DECLARE

val NUMBER;

BEGIN

FOR I IN (SELECT TABLE_NAME FROM USER_TABLES order by table_name) LOOP

EXECUTE IMMEDIATE 'SELECT count(*) FROM ' || i.table_name INTO val;

DBMS_OUTPUT.PUT_LINE(i.table_name || chr(9) || val );

END LOOP;

END;

/

在源和目标端分别执行，然后比较即可：

$ sqlplus -S hr/oracle@dbsrc< t.sql > src.txt

$ sqlplus -S hr/oracle@dbtgt< t.sql > tgt.txt

$ cat src.txt

COUNTRIES 25

DEPARTMENTS 27

EMPLOYEES 107

JOBS 19

JOB_HISTORY 10

LOCATIONS 23

REGIONS 4

$ diff src.txt tgt.txt

以下是有差异时的输出：

$ diff src.txt tgt.txt

2,3c2,3

< DEPARTMENTS 27

< EMPLOYEES 107

---

> DEPARTMENTS 26

> EMPLOYEES 117

$ diff -y src.txt tgt.txt

COUNTRIES 25 COUNTRIES 25

DEPARTMENTS 27 | DEPARTMENTS 26

EMPLOYEES 107 | EMPLOYEES 117

JOBS 19 JOBS 19

JOB_HISTORY 10 JOB_HISTORY 10

LOCATIONS 23 LOCATIONS 23

REGIONS 4 REGIONS 4

$ diff -y src.txt tgt.txt |grep '|'

DEPARTMENTS 27 | DEPARTMENTS 26

EMPLOYEES 107 | EMPLOYEES 117

这个脚本最大的缺陷是只能串行执行，需要改成支持并行

另一个问题是如何让DBMS_OUTPUT.PUT_LINE即时冲刷到stdout

这个问题的答案是没有，所以可以考虑将中间结果插入到临时表。

以下是产生某schema种表的列表的SQL:

-- tlist.sql

-- no column header

set heading off

-- not echo command

set echo off

-- supress terminal output

set termout off

set pages 999

-- no row count

set feedback off

spool table.lst

select table_name from user_tables;

spool off

运行如下：

$ sqlplus -S hr/oracle@orclpdb1 < tlist.sql

REGIONS

COUNTRIES

LOCATIONS

DEPARTMENTS

JOBS

EMPLOYEES

JOB_HISTORY

$ cat table.lst

REGIONS

COUNTRIES

LOCATIONS

DEPARTMENTS

JOBS

EMPLOYEES

JOB_HISTORY

如果要比较表中的内容，可以将每一行的所有列拼接，然后计算哈希值，然后将所有行的哈希值累加后比较即可。例如dbms_utility.get_hash_value

参考: http://www.dba-oracle.com/t_count_rows_all_tables_in_schema.htm