Compare the rowcount from tables of two databases

Shell Programming and Scripting
1

All,

I have to create a shell script to comapre the rowcount of 120 tables from two oracle databases. In fact, using informatica the data is loading from 120 source tables to 120 staging tables. After that, they want to have a shell script to comapre the rowcount of all these tables.

1) I think we can spool the rowcount to two files. But what should be the best strategy to handle this 120 tables? If I hard code the 120 tables in my script to get the rwocount, the script may not run later if the tables got changed.

2)Also, how do we compare the two spool files with rowcount?

Can somebody help here pls?

Thanks
Maya

2

Hi,

Its quite interesting question..

I think u should read the file from some directory after spooling.

  1. Within a 'for loop' u can access the file one by one.(table change may not effect)
  2. then within the do statement u can use: either awk/WC -l to count the rows.

or u can compare two file using:wc -l <file1> file2 /// comp -l file1 file2

Please try this..
:b:

3

Thanks Jdash

1) what is the way of getting rowcount for 120 tables ?

Select count(*) from table1;

select count(*) from table 2;

like that if I list all 120 tables, the code won't be flexible.

2)Also, do we need to have one spool file for 120 tables ? or one spool file for one table? what would be the best method?

3)Can somebody pls paste some code here ? for spool and compare

Thanks
Maya

4

Hai.. here is a sample snippet..
First write all your 120 tables names in a file, say, "tables.lst"..


for table in `cat tables.lst`
do
   sqlplus -s << EOF >>file_spool.txt 2>file_err.txt
   select ${table}||':'||count(*) from ${table};
EOF
done

First_tbl_cnt=`head -1 file_spool.txt | cut -d":" -f2`
awk -F":" -v cnt=${First_tbl_cnt} '$2!=cnt {print $0}' file_spool.txt

Now you will get the list of tables along with the actual record count which ever is not having the same record count as your first table. If nothing displayed, then it means that all your table record counts are same..

My worry is the 120 SQLPLUS login operations you need to do here..

Thanks,
Ramkrix