Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE TEST_TABLE
 (COL_1  NUMBER NOT NULL,
  COL_2  NUMBER,
  COL_3  NUMBER NOT NULL);
CREATE INDEX TEST_TABLE_1
  ON TEST_TABLE (COL_1, COL_2);
CREATE INDEX TEST_TABLE_2
  ON TEST_TABLE (COL_2);
CREATE INDEX TEST_TABLE_3
  ON TEST_TABLE (COL_3);
WITH cteIndex_column_info AS
  (SELECT ic.INDEX_OWNER,
          ic.INDEX_NAME,
          1 AS COLUMN_COUNT,
          CASE
            WHEN tc.NULLABLE = 'Y' THEN 1
            ELSE 0
          END AS NULLABLE_COLUMN_COUNT
     FROM ALL_INDEXES i
     INNER JOIN ALL_IND_COLUMNS ic
       ON ic.INDEX_OWNER = i.OWNER AND
          ic.INDEX_NAME  = i.INDEX_NAME
     INNER JOIN ALL_TAB_COLUMNS tc
       ON tc.OWNER       = i.TABLE_OWNER AND
          tc.TABLE_NAME  = i.TABLE_NAME AND
          tc.COLUMN_NAME = ic.COLUMN_NAME)
SELECT INDEX_OWNER,
       INDEX_NAME,
       CASE
         WHEN SUM(COLUMN_COUNT) = SUM(NULLABLE_COLUMN_COUNT) THEN 'PARTIAL'
         ELSE 'FULL'
       END AS INDEX_TYPE
  FROM cteIndex_column_info
  WHERE INDEX_NAME LIKE 'TEST_TABLE%'
  GROUP BY INDEX_OWNER,
           INDEX_NAME
  ORDER BY INDEX_OWNER,
           INDEX_NAME


INDEX_OWNER
INDEX_NAME
INDEX_TYPE


FIDDLE_GHRYTHZKYZBFTHNZMUUZ
TEST_TABLE_1
FULL

FIDDLE_GHRYTHZKYZBFTHNZMUUZ
TEST_TABLE_2
PARTIAL

FIDDLE_GHRYTHZKYZBFTHNZMUUZ
TEST_TABLE_3
FULL

INDEX_OWNER	INDEX_NAME	INDEX_TYPE
FIDDLE_GHRYTHZKYZBFTHNZMUUZ	TEST_TABLE_1	FULL
FIDDLE_GHRYTHZKYZBFTHNZMUUZ	TEST_TABLE_2	PARTIAL
FIDDLE_GHRYTHZKYZBFTHNZMUUZ	TEST_TABLE_3	FULL