List tables with their primary keys (PKs) in Oracle database

Query below lists:

(A) all tables and their primary keys constraint (PK) accessible to the current user in Oracle database

(B) all tables and their primary keys constraint (PK) in Oracle database

Query was executed under the Oracle 12c Database version.

Query

A. Tables and their primary keys constraint accessible to the current user

select tab.owner as schema_name,
       tab.table_name,
       acc.constraint_name,
       LISTAGG(acc.column_name,',')
              WITHIN GROUP (order by acc.position) as columns,
       con.status
from sys.all_tables tab
left join sys.all_constraints con
          on con.owner = tab.owner
          and con.table_name = tab.table_name
          and con.constraint_type = 'P'
left join sys.all_cons_columns acc 
          on con.owner = acc.owner
          and con.constraint_name = acc.constraint_name
where tab.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
      'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS',
      'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
      'WKPROXY','WMSYS','XDB','APEX_040000','APEX_040200', 'DIP', 
      'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
      'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'WKSYS', 'PUBLIC',
      'OUTLN', 'APEX_PUBLIC_USER')
group by tab.owner,
         tab.table_name,
         acc.constraint_name,
         con.status
order by tab.owner,
         tab.table_name;

B. If you have privilege on dba_tab_columns and dba_tables

select tab.owner as schema_name,
       tab.table_name,
       acc.constraint_name,
       LISTAGG(acc.column_name,',')
              WITHIN GROUP (order by acc.position) as columns,
       con.status
from sys.dba_tables tab
left join sys.dba_constraints con
          on con.owner = tab.owner
          and con.table_name = tab.table_name
          and con.constraint_type = 'P'
left join sys.dba_cons_columns acc
          on con.owner = acc.owner
          and con.constraint_name = acc.constraint_name
where tab.owner not in ('ANONYMOUS','CTXSYS','DBSNMP','EXFSYS', 'LBACSYS', 
      'MDSYS', 'MGMT_VIEW','OLAPSYS','OWBSYS','ORDPLUGINS', 'ORDSYS',
      'SI_INFORMTN_SCHEMA','SYS','SYSMAN','SYSTEM', 'TSMSYS','WK_TEST',
      'WKPROXY','WMSYS','XDB','APEX_040000','APEX_040200', 'DIP', 
      'FLOWS_30000','FLOWS_FILES','MDDATA', 'ORACLE_OCM', 'XS$NULL',
      'SPATIAL_CSW_ADMIN_USR', 'SPATIAL_WFS_ADMIN_USR', 'WKSYS', 'PUBLIC',
      'OUTLN', 'APEX_PUBLIC_USER')
group by tab.owner,
         tab.table_name,
         acc.constraint_name,
         con.status
order by tab.owner,
         tab.table_name;