During any Database Migration whether you do from Oracle to PostgreSQL or PostgreSQL to Oracle using tools like Oracle Golden Gate, HVR or Qlik replicate, As DBA our goal is to validate table level row count on both source and target databases.

1. Getting table level row count is always tricky when it comes to oltp databases like oracle, postgres & db2 since select count(*) from table will be more realistic vs you get row_count from alltable or tuple level or something since table level stats needs to preformed in order to get real time count
2. So how to achieve it? Since I aren’t developer so went in different path but it worked or for me
3. Created function in postgresql database to get all tables row count from it and let calling that function in sql query
4. Similar created Oracle View to get tables row count.
5. Using both tables data joined on table name using regular sql basic
6. Written Python program where it get data from step 3 and load into Oracle database table.
7. You can integrate this SQL Query either with you reporting tools, excel or OEM Dashboards.

create function count_rows_of_table(
  schema    text,
  tablename text
  )
  returns   integer
 
  security  invoker
  language  plpgsql
as
$body$
declare
  query_template constant text not null :=
    '
      select count(*) from "?schema"."?tablename"
    ';
 
  query constant text not null :=
    replace(
      replace(
        query_template, '?schema', schema),
     '?tablename', tablename);
 
  result int not null := -1;
begin
  execute query into result;
  return result;
end;
$body$;

Using this sql query can able to get row counts for all postgres tables.

select table_schema as pg_table_schema, table_name as pg_table_name, count_rows_of_table(table_schema, table_name) as pg_rows 
from information_schema.tables where  table_schema not in ('pg_catalog', 'information_schema') 
and table_type = 'BASE TABLE' order by 1 asc, 3 desc;

Run the Python Script

3. Target Oracle side, created view to get tables row count using below query!

SQL> CREATE OR REPLACE VIEW DBADEEDS_ORACLEDB.OR_TBROWCONT AS (SELECT owner,
OBJECT_NAME as table_name,
       TO_NUMBER (
           EXTRACTVALUE (
               DBMS_XMLGEN.getXMLtype (
                      'select /*+ PARALLEL(8) */ count(*) cnt from "YOURORACLESCHEMANAME".'
                   || OBJECT_NAME),
               '/ROWSET/ROW/CNT'))
           or_rowscount
  FROM ALL_OBJECTS where OBJECT_TYPE = 'TABLE' AND owner = 'YOUR ORACLE SCHEMA');

Using these tables  SELECT * FROM  PG_TBROWCONT & SELECT * FROM DBADEEDS_ORACLEDB.OR_TBROWCONT

select p.pg_table_schema, o.owner as oracle_schema, p.pg_table_name, o.table_name as oracle_table_name, p.pg_rows, dbadeeds_oracledb.or_rowscount,
round ( round (o.or_rowscount)-round(p.pg_rows)) as diff
from PG_TBROWCONT p , ORACLE.OR_TBROWCONT o
where upper(p.pg_table_name) = upper(o.table_name)
order by diff asc

Results: