Oracle 11g Release 2

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

create table t1 ("col.1", "col.2", "col.3") as
select 'y', null, 'm1' from dual
union all select 'y', 22, 'm2' from dual
union all select 'n', 45, 'm2' from dual
union all select 'y', 10, 'm5' from dual;
4 rows affected

create table t2 ("col.1", "col.2", "col.3") as
select 1, 'germany', 'xxx' from dual
union all select 2, 'england', 'xxx' from dual
union all select 3, 'germany', 'uzt' from dual
union all select 3, 'germany', 'vvx' from dual
union all select 8, 'US', 'XXX' from dual;
5 rows affected

select  table_name,
        column_name,
        xmlquery('/ROWSET/ROW/COL/text()'
            passing xmltype(dbms_xmlgen.getxml( 'select "' || column_name  || '" as col from (select "' || column_name ||'", rank () over (order by count(*) desc) as rnk from ' 
            || table_name || ' Group by "' || column_name || '") where rnk = 1'))
            returning content) as C
from all_tab_columns
where owner = user
and table_name in ('T1', 'T2', 'T3', 'T4');


TABLE_NAME
COLUMN_NAME
C


T1
col.1
y

T1
col.2
224510

T1
col.3
m2

T2
col.1
3

T2
col.2
germany

T2
col.3
xxx

TABLE_NAME	COLUMN_NAME	C
T1	col.1	y
T1	col.2	224510
T1	col.3	m2
T2	col.1	3
T2	col.2	germany
T2	col.3	xxx