Query below lists:

(A) % of tables accessible to the current user in Oracle database that don't have primary keys

(B) % of tables in Oracle database that don't have primary keys

Query was executed under the Oracle9i Database version.

Query

A. Tables accessible to the current user

select count(*) as table_count,      
       sum(case when pks.table_name is null 
                    then 1
                else 0
           end) as no_pk_tables,
       cast(100 * sum(case when pks.table_name is null 
                    then 1
                else 0
           end) / count(*) as decimal(3, 1))
       as no_pk_percent
from sys.all_tables t 
left join (select distinct owner,
                           table_name
           from sys.all_constraints 
           where constraint_type = 'P'
           ) pks
           on t.owner = pks.owner
              and t.table_name = pks.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',
   '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');

B. If you have privilege on dba_tables and dba_constraints

select count(*) as table_count,      
       sum(case when pks.table_name is null 
                    then 1
                else 0
           end) as no_pk_tables,
       cast(100 * sum(case when pks.table_name is null 
                    then 1
                else 0
           end) / count(*) as decimal(3, 1))
       as no_pk_percent
from sys.dba_tables t 
left join (select distinct owner,
                           table_name
           from sys.dba_constraints 
           where constraint_type = 'P'
           ) pks
           on t.owner = pks.owner
              and t.table_name = pks.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',
   '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');

Columns

• table_count - number of tables in a database
• no_pk_tables - number of tables without a primary key in a database
• no_pk_percent - percentage of tables without primary key in a database

Rows

Query returns one row

Sample results