Queries below list most popular table columns.

Query was executed under the Oracle9i Database version.

Query

A. Tables accessible to the current user

select col.column_name,
       count(*) as table_count,
       cast(100 
            * count(*) / 
            (select count(*)
             from sys.all_tables
             -- excluding some Oracle maintained schemas
             where owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 
                    'LBACSYS', 'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS',
                    'ORDPLUGINS', 'ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA',
                    'SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST','WKSYS',
                    'WKPROXY','WMSYS','XDB','APEX_040000', 
                    'APEX_PUBLIC_USER','DIP', 'FLOWS_30000','FLOWS_FILES',
                    'MDDATA', 'ORACLE_OCM', 'XS$NULL', 
                    'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 
                    'PUBLIC')) 
            as decimal(4, 1)) as table_percentage                   
from sys.all_tab_columns col
inner join sys.all_tables tab on col.owner = tab.owner
                                and col.table_name = tab.table_name
-- excluding some Oracle maintained schemas
where col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
   'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN',
   'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
   'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
   'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
   'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC', 'WKSYS')
group by col.column_name
order by count(*) desc,
         column_name;         

B. If you have privilege on dba_tables and dba_tab_columns

select col.column_name,
       count(*) as table_count,
       cast(100 
            * count(*) / 
            (select count(*)
             from sys.dba_tables
             -- excluding some Oracle maintained schemas
             where owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 
                    'LBACSYS', 'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS',
                    'ORDPLUGINS', 'ORDSYS','OUTLN', 'SI_INFORMTN_SCHEMA',
                    'SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST','WKSYS',
                    'WKPROXY','WMSYS','XDB','APEX_040000', 
                    'APEX_PUBLIC_USER','DIP', 'FLOWS_30000','FLOWS_FILES',
                    'MDDATA', 'ORACLE_OCM', 'XS$NULL', 
                    'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 
                    'PUBLIC')) 
            as decimal(4, 1)) as table_percentage                   
from sys.dba_tab_columns col
inner join sys.dba_tables tab on col.owner = tab.owner
                                and col.table_name = tab.table_name
-- excluding some Oracle maintained schemas
where col.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
   'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS','OUTLN', 
   'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
   'WKPROXY','WMSYS','XDB','APEX_040000', 'APEX_PUBLIC_USER','DIP', 
   'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
   'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'PUBLIC', 'WKSYS')
group by col.column_name
order by count(*) desc,
         column_name;

Columns

• column_name - column name
• table_count - number of tables the specific column name appears in
• table_percentage - percentage of tables the specific column name appears in

Rows

• One row represents one column name in a database
• Scope of rows: (A) tables' columns, along with the number of tables they appear in and percentage of tables they appear in. All tables accessible to the current user in Oracle database, (B) tables' columns, along with the number of tables they appear in and percentage of tables they appear in. All tables in Oracle database
• Ordered by number of tables column appear in (descending), column name

Sample results