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


select atc.table_name, atc.column_name, x.value, x.value_count,
  rank() over (partition by atc.table_name, atc.column_name
    order by x.value_count desc) as rnk
from all_tab_columns atc
cross join xmltable(
  '/ROWSET/ROW'
  passing xmltype(dbms_xmlgen.getxml(
         'select "' || column_name  || '" as value, count(*) as value_count '
      || 'from ' || table_name || ' '
      || 'group by "' || column_name || '"'))
  columns value varchar2(4000) path 'VALUE',
          value_count number path 'VALUE_COUNT'
) x
where atc.owner = user
and atc.table_name in ('T1', 'T2', 'T3', 'T4');


TABLE_NAME
COLUMN_NAME
VALUE
VALUE_COUNT
RNK


T1
col.1
y
3
1

T1
col.1
n
1
2

T1
col.2
null
1
1

T1
col.2
22
1
1

T1
col.2
45
1
1

T1
col.2
10
1
1

T1
col.3
m2
2
1

T1
col.3
m1
1
2

T1
col.3
m5
1
2

T2
col.1
3
2
1

T2
col.1
1
1
2

T2
col.1
2
1
2

T2
col.1
8
1
2

T2
col.2
germany
3
1

T2
col.2
england
1
2

T2
col.2
US
1
2

T2
col.3
xxx
2
1

T2
col.3
vvx
1
2

T2
col.3
uzt
1
2

T2
col.3
XXX
1
2


select table_name, column_name, value
from (
  select atc.table_name, atc.column_name, x.value, x.value_count,
    rank() over (partition by atc.table_name, atc.column_name
      order by x.value_count desc) as rnk
  from all_tab_columns atc
  cross join xmltable(
    '/ROWSET/ROW'
    passing xmltype(dbms_xmlgen.getxml(
           'select "' || column_name  || '" as value, count(*) as value_count '
        || 'from ' || table_name || ' '
        || 'group by "' || column_name || '"'))
    columns value varchar2(4000) path 'VALUE',
            value_count number path 'VALUE_COUNT'
  ) x
  where atc.owner = user
  and atc.table_name in ('T1', 'T2', 'T3', 'T4')
)
where rnk = 1;


TABLE_NAME
COLUMN_NAME
VALUE


T1
col.1
y

T1
col.2
null

T1
col.2
22

T1
col.2
45

T1
col.2
10

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
TABLE_NAME	COLUMN_NAME	VALUE
T1	col.1	y
T1	col.2	null
T1	col.2	22
T1	col.2	45
T1	col.2	10
T1	col.3	m2
T2	col.1	3
T2	col.2	germany
T2	col.3	xxx