List tables which have specific column with number of rows for a given requirement.

select q'[select ']'||table_name||q'[' as table_name, count(*) from ]'||table_name||q'[ where COL1='VAL1';]' from dba_tab_columns where column_name='COL1' and upper(owner) ='USER';