Query below lists:

(A) all tables without primary keys defined accessible to the current user in Oracle database

(B) all tables without primary keys defined in Oracle database

Query was executed under the Oracle12c Database version.

Query

A. Tables without primary keys defined accessible to the current user

select t.owner as schema_name,
       t.table_name
from sys.all_tables t
left join sys.all_constraints c
          on t.owner = c.owner
          and t.table_name = c.table_name
          and c.constraint_type = 'P'
where c.constraint_type is null
order by t.owner,
         t.table_name;

B.If you have privilege on dba_tables and dba_constraints

select t.owner as schema_name,
       t.table_name
from sys.dba_tables t
left join sys.dba_constraints c
          on t.owner = c.owner
          and t.table_name = c.table_name
          and c.constraint_type = 'P'
where c.constraint_type is null
order by t.owner,
         t.table_name;

Columns

• schema_name - table schema name
• table_name - table name

Rows

• One row represents one table
• Scope of rows: all tables owned by current user
• Ordered by schema name and table name

Sample results

You can see tables without primary keys in our test database: