Multiple Tables Record Count by a Single Query

Sometimes DBA need to verify or compare multiple tables record count. It is really time consuming to compare one by one table count. So, by using this query, you will able to get record count report for multiple tables in Oracle.

set serveroutput on
declare
c integer;
cursor cur_rec is
select 'Select count(*) from ' || owner || '.' || table_name AS C_QUERY, OWNER, TABLE_NAME
FROM ALL_TAB_COLUMNS
WHERE  owner IN ('SCOTT', 'SYSTEM') ;


begin
    FOR rec in cur_rec
    	LOOP
    		begin
			EXECUTE IMMEDIATE rec.c_query into c;
			dbms_output.put_line('Schema.TableName: ' || rec.OWNER || '.' || rec.TABLE_NAME ||  ' count:' || c);
		end;
	END LOOP;
end;
/