Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE OR REPLACE FUNCTION SNK_VERIFICA_NOME_IDX(P_INDICE VARCHAR2)
RETURN VARCHAR2
IS
  P_COUNT INT;
  P_QTD INT;
  P_NOVONOME VARCHAR2(100);
BEGIN
  P_NOVONOME := P_INDICE;
  SELECT COUNT(1) INTO P_COUNT FROM USER_INDEXES WHERE INDEX_NAME = P_INDICE;      
  IF P_COUNT <> 0 THEN
    P_QTD := 1;        
    WHILE (P_COUNT <> 0)
    LOOP
      P_NOVONOME := P_INDICE || P_QTD;
      SELECT COUNT(1) INTO P_COUNT FROM USER_INDEXES WHERE INDEX_NAME = P_INDICE || P_QTD;          
      P_QTD := P_QTD + 1;
    END LOOP;
  END IF;  
  RETURN P_NOVONOME;
END;
/

CREATE OR REPLACE PROCEDURE STP_GERA_INDICES_FKS
AS
BEGIN
DECLARE
  P_COUNT INT;
  P_COMANDO VARCHAR2(4000);
  P_TABELA VARCHAR2(50);
  P_INDICE VARCHAR2(50);
  P_COLUNAS VARCHAR2(1000);
  P_QTD INT;
  CURSOR CURIDX IS
  SELECT TABLE_NAME, SUBSTR(TABLE_NAME,1,10) || '_IDX_FK_' || SUBSTR(REPLACE(FK_COLUMNS,',','_'),1,10) AS INDEX_NAME, FK_COLUMNS AS INDEX_COLUMNS FROM
  (SELECT CASE WHEN B.TABLE_NAME IS NULL THEN 'unindexed' ELSE 'indexed' END AS STATUS,
          A.TABLE_NAME, A.CONSTRAINT_NAME, A.FK_COLUMNS, B.INDEX_NAME, B.INDEX_COLUMNS
     FROM (SELECT A.TABLE_NAME, A.CONSTRAINT_NAME,
                  LISTAGG(A.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY A.POSITION) FK_COLUMNS
             FROM USER_CONS_COLUMNS A, USER_CONSTRAINTS B
            WHERE A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
              AND B.CONSTRAINT_TYPE = 'R'
              AND A.TABLE_NAME LIKE 'T%'
         GROUP BY A.TABLE_NAME, A.CONSTRAINT_NAME) A,
          (SELECT TABLE_NAME, INDEX_NAME,
                  LISTAGG(C.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY C.COLUMN_POSITION) INDEX_COLUMNS
             FROM USER_IND_COLUMNS C
            WHERE C.TABLE_NAME LIKE 'T%'
         GROUP BY TABLE_NAME, INDEX_NAME) B
    WHERE A.TABLE_NAME = B.TABLE_NAME(+)
      AND B.INDEX_COLUMNS(+) LIKE A.FK_COLUMNS || '%'
    ORDER BY 1 DESC, 2) X
  WHERE STATUS = 'unindexed';
BEGIN
  OPEN CURIDX;
  LOOP
    FETCH CURIDX INTO P_TABELA, P_INDICE, P_COLUNAS;
    EXIT WHEN CURIDX%NOTFOUND;
    P_INDICE := SNK_VERIFICA_NOME_IDX(P_INDICE);
    P_COMANDO := 'CREATE INDEX ' || P_INDICE || ' ON ' || P_TABELA || '(' || P_COLUNAS || ') tablespace SANKIND;';
    DBMS_OUTPUT.PUT_LINE(P_COMANDO);
  END LOOP;
  CLOSE CURIDX;
END;
END;
/
CREATE TABLE TABCDEFGHI1 (id NUMBER PRIMARY KEY);
CREATE TABLE TABCDEFGHI2 (
  t_id NUMBER
       PRIMARY KEY,
  id   NUMBER
       REFERENCES TABCDEFGHI1(id)
);
CREATE TABLE TABCDEFGHI3 (
  id NUMBER
     REFERENCES TABCDEFGHI1(id)
     REFERENCES TABCDEFGHI2(t_id)
);
    SELECT A.TABLE_NAME,
           A.CONSTRAINT_NAME,
           LISTAGG(A.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY A.POSITION) FK_COLUMNS
    FROM   USER_CONS_COLUMNS A,
           USER_CONSTRAINTS B
    WHERE  A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
    AND    B.CONSTRAINT_TYPE = 'R'
    AND    A.TABLE_NAME LIKE 'T%'
    GROUP BY
           A.TABLE_NAME,
           A.CONSTRAINT_NAME


TABLE_NAME
CONSTRAINT_NAME
FK_COLUMNS


TABCDEFGHI2
SYS_C00189581
ID

TABCDEFGHI3
SYS_C00189582
ID

TABCDEFGHI3
SYS_C00189583
ID


  SELECT TABLE_NAME,
         INDEX_NAME,
         LISTAGG(C.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY C.COLUMN_POSITION) INDEX_COLUMNS
  FROM   USER_IND_COLUMNS C
  WHERE  C.TABLE_NAME LIKE 'T%'
  GROUP BY TABLE_NAME, INDEX_NAME


TABLE_NAME
INDEX_NAME
INDEX_COLUMNS


TABCDEFGHI1
SYS_C00189579
ID

TABCDEFGHI2
SYS_C00189580
T_ID


  SELECT CASE WHEN B.TABLE_NAME IS NULL THEN 'unindexed' ELSE 'indexed' END AS STATUS,
         A.TABLE_NAME,
         A.CONSTRAINT_NAME,
         A.FK_COLUMNS,
         B.INDEX_NAME,
         B.INDEX_COLUMNS
  FROM   (
    SELECT A.TABLE_NAME,
           A.CONSTRAINT_NAME,
           LISTAGG(A.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY A.POSITION) FK_COLUMNS
    FROM   USER_CONS_COLUMNS A,
           USER_CONSTRAINTS B
    WHERE  A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
    AND    B.CONSTRAINT_TYPE = 'R'
    AND    A.TABLE_NAME LIKE 'T%'
    GROUP BY
           A.TABLE_NAME,
           A.CONSTRAINT_NAME
  ) A,
  (
    SELECT TABLE_NAME,
           INDEX_NAME,
           LISTAGG(C.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY C.COLUMN_POSITION) INDEX_COLUMNS
    FROM   USER_IND_COLUMNS C
    WHERE  C.TABLE_NAME LIKE 'T%'
    GROUP BY TABLE_NAME, INDEX_NAME
  ) B
  WHERE  A.TABLE_NAME = B.TABLE_NAME(+)
  AND    B.INDEX_COLUMNS(+) LIKE A.FK_COLUMNS || '%'
  ORDER BY 1 DESC, 2


STATUS
TABLE_NAME
CONSTRAINT_NAME
FK_COLUMNS
INDEX_NAME
INDEX_COLUMNS


unindexed
TABCDEFGHI2
SYS_C00189581
ID
null
null

unindexed
TABCDEFGHI3
SYS_C00189583
ID
null
null

unindexed
TABCDEFGHI3
SYS_C00189582
ID
null
null


SELECT TABLE_NAME,
       SUBSTR(TABLE_NAME,1,10) || '_IDX_FK_' || SUBSTR(REPLACE(FK_COLUMNS,',','_'),1,10) AS INDEX_NAME,
       FK_COLUMNS AS INDEX_COLUMNS
FROM   (
  SELECT CASE WHEN B.TABLE_NAME IS NULL THEN 'unindexed' ELSE 'indexed' END AS STATUS,
         A.TABLE_NAME,
         A.CONSTRAINT_NAME,
         A.FK_COLUMNS,
         B.INDEX_NAME,
         B.INDEX_COLUMNS
  FROM   (
    SELECT A.TABLE_NAME,
           A.CONSTRAINT_NAME,
           LISTAGG(A.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY A.POSITION) FK_COLUMNS
    FROM   USER_CONS_COLUMNS A,
           USER_CONSTRAINTS B
    WHERE  A.CONSTRAINT_NAME = B.CONSTRAINT_NAME
    AND    B.CONSTRAINT_TYPE = 'R'
    AND    A.TABLE_NAME LIKE 'T%'
    GROUP BY
           A.TABLE_NAME,
           A.CONSTRAINT_NAME
  ) A,
  (
    SELECT TABLE_NAME,
           INDEX_NAME,
           LISTAGG(C.COLUMN_NAME, ',') WITHIN GROUP (ORDER BY C.COLUMN_POSITION) INDEX_COLUMNS
    FROM   USER_IND_COLUMNS C
    WHERE  C.TABLE_NAME LIKE 'T%'
    GROUP BY TABLE_NAME, INDEX_NAME
  ) B
  WHERE  A.TABLE_NAME = B.TABLE_NAME(+)
  AND    B.INDEX_COLUMNS(+) LIKE A.FK_COLUMNS || '%'
  ORDER BY 1 DESC, 2
) X
WHERE STATUS = 'unindexed';


TABLE_NAME
INDEX_NAME
INDEX_COLUMNS


TABCDEFGHI2
TABCDEFGHI_IDX_FK_ID
ID

TABCDEFGHI3
TABCDEFGHI_IDX_FK_ID
ID

TABCDEFGHI3
TABCDEFGHI_IDX_FK_ID
ID


BEGIN
  DBMS_OUTPUT.ENABLE();
  STP_GERA_INDICES_FKS();
END;
/
1 rows affected

dbms_output:
CREATE INDEX TABCDEFGHI_IDX_FK_ID ON TABCDEFGHI2(ID) tablespace SANKIND;
CREATE INDEX TABCDEFGHI_IDX_FK_ID ON TABCDEFGHI3(ID) tablespace SANKIND;
CREATE INDEX TABCDEFGHI_IDX_FK_ID ON TABCDEFGHI3(ID) tablespace SANKIND;
TABLE_NAME	CONSTRAINT_NAME	FK_COLUMNS
TABCDEFGHI2	SYS_C00189581	ID
TABCDEFGHI3	SYS_C00189582	ID
TABCDEFGHI3	SYS_C00189583	ID
STATUS	TABLE_NAME	CONSTRAINT_NAME	FK_COLUMNS	INDEX_NAME	INDEX_COLUMNS
unindexed	TABCDEFGHI2	SYS_C00189581	ID	null	null
unindexed	TABCDEFGHI3	SYS_C00189583	ID	null	null
unindexed	TABCDEFGHI3	SYS_C00189582	ID	null	null
TABLE_NAME	INDEX_NAME	INDEX_COLUMNS
TABCDEFGHI2	TABCDEFGHI_IDX_FK_ID	ID
TABCDEFGHI3	TABCDEFGHI_IDX_FK_ID	ID
TABCDEFGHI3	TABCDEFGHI_IDX_FK_ID	ID