主键索引

select t.constraint_name,
       t.table_name,
       (select  listagg(a.column_name, ',') within group(order by a.position asc) table_name
          from dba_cons_columns a
         where a.constraint_name not like 'BIN$%' and a.constraint_name=t.constraint_name and a.table_name=t.table_name and a.owner=t.owner) COLS
  from dba_constraints t
 where t.CONSTRAINT_TYPE = 'P'
   and t.TABLE_NAME not like 'BIN$%'
   and t.owner in('DATACENTER','JHFILE','JHCDR','JHNIS','JHLCPV6','REPORT')
   order by t.table_name; --主键
 
   select t.index_name,
       t.table_name,
       (select  listagg(a.column_name, ',') within group(order by a.COLUMN_POSITION asc) table_name
          from dba_ind_columns a
         where a.TABLE_NAME not like 'BIN$%' and a.INDEX_NAME=t.index_name and a.table_name=t.table_name and a.TABLE_OWNER=t.table_owner) COLS
  from dba_indexes t
 where  t.index_name not like 'SYS_IL%'
   and t.table_owner in('DATACENTER','JHFILE','JHCDR','JHNIS','JHLCPV6','REPORT')
    order by t.table_name; --索引

查询所有没有不是PK打头的主键
select t.constraint_name,
       t.table_name,
       (select  listagg(a.column_name, ',') within group(order by a.position asc) table_name
          from dba_cons_columns a
         where a.constraint_name not like 'BIN$%' and a.constraint_name=t.constraint_name and a.table_name=t.table_name and a.owner=t.owner)
  from dba_constraints t
 where t.CONSTRAINT_TYPE = 'P'
   and t.TABLE_NAME not like 'BIN$%'
  and t.constraint_name not like 'PK%'
   and t.owner in('DATACENTER','JHFILE','JHCDR','JHNIS','JHLCPV6','REPORT'); --主键

主键 索引

主键索引