#!/usr/local/bin/perl -w

use strict;
use DBI;
use Env;

my @DB = ('DB1','DB2');
my $user = "user";
my $passwd = "passwd";
my $schema = "USER";
my $DBHome = "/U01/oracle/ora817";
$ENV{"ORACLE_HOME"} = $DBHome;
my %dbs;

&query_databases();
my $result = do_compare();

sub query_databases {
  for (@DB)  {
    my ($dbh,$sth,@TABLE_NAMES,$TABLE_NAME,$rowcount,%result);
    my $db = $_;

# Login to the database
    $dbh = DBI->connect("dbi:Oracle:$db",$user,$passwd,{AutoCommit => 0, RaiseError
=> 1}) || die("Oracle login FAILED, $DBI::errstr");

    $sth = $dbh->prepare("select TABLE_NAME from dba_tables where owner=?");
    $sth->execute( $schema ) || die $dbh->errstr;

    while ( $TABLE_NAME = $sth->fetchrow_array ) {
      push(@TABLE_NAMES,$TABLE_NAME);
    }
    die $sth->errstr if $sth->err;
    for $TABLE_NAME ( @TABLE_NAMES ) {
       $sth = $dbh->prepare("select count(*) from $schema.$TABLE_NAME");
       $sth->execute || die $dbh->errstr;

       while ($rowcount = $sth->fetchrow_array ) {
          $dbs{$db}->{$TABLE_NAME} = $rowcount;
       }
    }
    $dbh->disconnect;
  }
}

sub do_compare {
  for my $key ( keys %dbs ) {
       print "Database is $key\n";
       for my $key2 ( sort keys %{$dbs{$key}} ) {
          print "$key2\t $dbs{$key}->{$key2}\n";
       }
   }
}