list_indexes.sql
--
--  インデックス一覧
--
-- history
--    2018/02/24 ORDER BY句の位置を修正
--    2018/03/03 INDEX_TYPEの表示位置を修正
--
SET ECHO OFF
SET FEEDBACK OFF
SET LINESIZE 500
SET PAGESIZE 50000
SET TERMOUT OFF
SET TRIMSPOOL ON
-- SPOOL pass includes_CONNECT_IDENTIFIER and SYSDATE
COLUMN SYSTEM_DATE    NEW_VALUE SYSTEM_DATE
SELECT '_'  || TO_CHAR(SYSDATE, 'YYYYMMDD_HH24MISS') AS SYSTEM_DATE FROM DUAL ;
SPOOL list_indexes_&&_CONNECT_IDENTIFIER.&&SYSTEM_DATE..log
-- Windows ↑,  UNIX/Linux では ↓ を有効に
-- SPOOL list_indexes_\&&_CONNECT_IDENTIFIER.\&&SYSTEM_DATE..log
BREAK ON TABLE_NAME ON INDEX_NAME ON CONSTRAINT_TYPE
COLUMN TABLE_NAME       FORMAT A30
COLUMN INDEX_TYPE       FORMAT A30
COLUMN INDEX_NAME       FORMAT A30
COLUMN CONSTRAINT_TYPE  FORMAT A21
COLUMN COLUMN_NAMES     FORMAT A300
SELECT DISTINCT
    A.TABLE_NAME
  , A.INDEX_TYPE
  , A.INDEX_NAME
  , A.CONSTRAINT_TYPE
  , NVL2(A.COLUMN_NAMES, '(' || A.COLUMN_NAMES || ')', NULL) AS COLUMN_NAMES
FROM(
    SELECT
         TA.TABLE_NAME
       , IX.INDEX_TYPE
       , NVL(IX.INDEX_NAME, '#no index#') AS INDEX_NAME
       , CASE CN.CONSTRAINT_TYPE
             WHEN 'C' THEN 'CONSTRAINT'   -- 表に対する検査制約
             WHEN 'P' THEN 'PRIMARY KEY'  -- 主キー
             WHEN 'U' THEN 'UNIQUE'       -- 一意のキー
             WHEN 'R' THEN 'REFERENCES'   -- 参照整合性
             WHEN 'V' THEN 'WITH CHECK'   -- ビューでのチェック・オプション付き
             WHEN 'O' THEN 'READ ONLY'    -- ビューでの読取り専用
             ELSE
                 CASE
                     WHEN IX.UNIQUENESS = 'UNIQUE INDEX' THEN
                         IX.UNIQUENESS
                 END
         END AS CONSTRAINT_TYPE
       , LISTAGG(IC.COLUMN_NAME || DECODE(IC.DESCEND, 'DESC', ' DESC'), ', ')
                    WITHIN GROUP(ORDER BY IC.COLUMN_POSITION)
                    OVER(PARTITION BY TA.TABLE_NAME, IX.INDEX_NAME) AS COLUMN_NAMES
    FROM USER_TABLES TA
    LEFT OUTER JOIN USER_INDEXES IX  -- 索引のないテーブルもある 
    ON  IX.TABLE_NAME  = TA.TABLE_NAME
    INNER JOIN USER_IND_COLUMNS IC
    ON  IC.TABLE_NAME  = IX.TABLE_NAME
    AND IC.INDEX_NAME  = IX.INDEX_NAME
    LEFT OUTER JOIN USER_CONSTRAINTS CN  -- 制約のないテーブルもある
    ON  CN.TABLE_NAME      = IX.TABLE_NAME
    AND CN.CONSTRAINT_NAME = IX.INDEX_NAME
    WHERE
        TA.DROPPED = 'NO' -- except trash box, since oracle10.1.0
    AND TA.TABLE_NAME NOT IN
        (
        'CHAINED_ROWS'  -- except Oracle side table
      , 'EXCEPTIONS'
      , 'PLAN_TABLE'
      , 'PLAN_TABLE$'
        )
    ) A
ORDER BY
    A.TABLE_NAME
  , A.INDEX_NAME ;
SPOOL OFF
CLEAR BREAKS
COLUMN TABLE_NAME       CLEAR
COLUMN INDEX_NAME       CLEAR
COLUMN CONSTRAINT_TYPE  CLEAR
SET ECHO ON
SET FEEDBACK ON
SET LINESIZE 80
参考
LISTAGG SQL language reference Oracle11.2.0 以降でサポートされた