0

Need a script which will compare a particular static table "T_HANDLING" present in Main Site and DR Site. so this script should tell whether the table data is in synch or not between main and DR database.

This can be a script or springboot service as well.

vinoth gct
  • 1
  • 1

1 Answers1

1

What I use:

select count(*) as MISMATCHED
  from (select * from PRIMARY_TABLE minus select * from BACKUP_TABLE)
union
select count(*) as MISMATCHED
  from (select * from BACKUP_TABLE minus select * from PRIMARY_TABLE);

Counts will be zero when the tables are not mismatched.

lightkeeper
  • 327
  • 1
  • 3
  • 11
  • I would use UNION ALL rather than UNION, and/or include a literal in each projection to distinguish the two counts. It will occasionally be informative to know which site is lacking records, and it will never harmful to include such a label. – APC Jan 20 '22 at 16:11
  • Thanks lightkeeper and APC for your answers. But in a SQL developer when i have 2 connections how can I execute this script when the backup table is from another connection or database ? – vinoth gct Jan 21 '22 at 04:27
  • Can you use the [Database Link](https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_5005.htm)? – David Lukas Jan 21 '22 at 07:25
  • My real script does include literals for identification. I compare a large number of tables, but just wanted to present something simple. My databases are on two different boxes, and I use DBLink to communicate between the two instances. Why use UNION ALL instead of UNION? – lightkeeper Jan 21 '22 at 12:30
  • @lightkeeper Performance. [what-is-the-difference-between-union-and-union-all](https://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all) – David Lukas Jan 21 '22 at 13:56