EBS: 通过表名查询索引清单

ORACLE  19C 通过表名查询索引清单

SELECT 
       IDX.TABLE_OWNER, 
       IDX.TABLE_NAME,
       IDX.TABLE_TYPE,
       IDX.OWNER, IDX.INDEX_NAME, 
       IDX.uniqueness, 
       IDX.TABLESPACE_NAME,
      -- IDXC.COLUMN_NAME,
      -- IDXC.COLUMN_POSITION  ,
       LISTAGG(IDXC.COLUMN_NAME, ', ') WITHIN GROUP (ORDER BY IDX.OWNER, IDX.INDEX_NAME, IDXC.COLUMN_POSITION )
       AS COLUMN_NAMES -- 列
      --, IDX.*
  FROM SYS.DBA_INDEXES IDX ,
       SYS.DBA_IND_COLUMNS  IDXC 
WHERE IDX.TABLE_NAME LIKE 'AP_INVOICES_ALL'
 AND IDX.INDEX_NAME = IDXC.INDEX_NAME
 AND IDX.OWNER = IDXC.index_owner 
GROUP BY  
       IDX.TABLE_OWNER, 
       IDX.TABLE_NAME,
       IDX.TABLE_TYPE,
       IDX.OWNER, IDX.INDEX_NAME,
       IDX.uniqueness, 
       IDX.TABLESPACE_NAME 
ORDER BY IDX.OWNER, IDX.TABLE_NAME, IDX.INDEX_NAME 

　　

2023/11/01