Query below lists:

(A) all primary keys constraints (PK) accessible to the current user in Oracle database

(B) all primary keys constraints (PK) in Oracle database

Query was executed under the Oracle 12c Database version.

See also: tables with their primary keys.

Query

A. Primary keys constraints accessible to the current user

select acc.owner as schema_name,
       acc.constraint_name,
       acc.table_name,
       LISTAGG(acc.column_name,',')
              WITHIN GROUP (order by acc.position) as columns
from sys.all_constraints con
join sys.all_cons_columns acc on con.owner = acc.owner
     and con.constraint_name = acc.constraint_name
where con.constraint_type = 'P'
      and acc.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_040200','APEX_PUBLIC_USER',
     'DIP','FLOWS_30000','FLOWS_FILES','MDDATA','ORACLE_OCM','XS$NULL',
     'SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR','WKSYS', 'PUBLIC')
      and acc.table_name not like 'BIN$%' 
group by acc.owner,
         acc.table_name,
         acc.constraint_name
order by acc.owner,
         acc.constraint_name;

B. If you have privilege on dba_constraints and dba_cons_columns

select acc.owner as schema_name,
       acc.constraint_name,
       acc.table_name,
       LISTAGG(acc.column_name,',')
              WITHIN GROUP (order by acc.position) as columns
from sys.dba_constraints con
join sys.dba_cons_columns acc on con.owner = acc.owner
     and con.constraint_name = acc.constraint_name
where con.constraint_type = 'P'
      and acc.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_040200','APEX_PUBLIC_USER',
     'DIP','FLOWS_30000','FLOWS_FILES','MDDATA','ORACLE_OCM','XS$NULL',
     'SPATIAL_CSW_ADMIN_USR','SPATIAL_WFS_ADMIN_USR', 'WKSYS', 'PUBLIC')
      and acc.table_name not like 'BIN$%'
group by acc.owner,
         acc.table_name,
         acc.constraint_name
order by acc.owner,
         acc.constraint_name;

Columns

• schema_name - PK schema name
• constraint_name - PK constraint name
• table_name - PK table name
• columns - list of PK columns separated with ','

Rows

• One row represents one primary key (table) in a database
• Scope of rows: all PK constraints in a database
• Ordered by schema and constraint name

Sample results