Query below lists:

(A) tables, along with the number of their columns. All tables accessible to the current user in Oracle database

(B) tables, along with the number of their columns. All tables in Oracle database

Query was executed under the Oracle9i Database version.

Query

A. Tables accessible to the current user

select t.owner as schema_name,
       t.table_name,       
       count(*) as column_count      
from sys.all_tables t
inner join sys.all_tab_columns col on t.owner = col.owner
                                    and t.table_name = col.table_name
-- excluding some Oracle maintained schemas
where t.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 t.owner,
         t.table_name
order by count(*) desc,
         t.owner,
         t.table_name;

B. If you have privilege on dba_tables and dba_tab_columns

select t.owner as schema_name,
       t.table_name,       
       count(*) as column_count      
from sys.dba_tables t
inner join sys.dba_tab_columns col on t.owner = col.owner
                                    and t.table_name = col.table_name
-- excluding some Oracle maintained schemas
where t.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 t.owner,
         t.table_name
order by count(*) desc,
         t.owner,
         t.table_name;

Columns

• schema_name - table owner, schema name
• table_name - table name
• column_count - number of colums in a specific table

Rows

• One row represents one table in a database
• Scope of rows: (A) tables, along with the number of their columns. All tables accessible to the current user in Oracle database, (B) tables, along with the number of their columns. All tables in Oracle database
• Ordered by number of columns in a table (descending), schema name, table name

Sample results