Existe uma tabela de consulta para a coluna ALL_CONSTRAINTS.CONSTRAINT_TYPE no Oracle DB?

Não há nenhuma, até onde eu saiba.

Se você der uma olhada no CREATE VIEWscript:

CREATE OR REPLACE FORCE VIEW SYS.ALL_CONSTRAINTS
(
   OWNER,
   CONSTRAINT_NAME,
   CONSTRAINT_TYPE,
   TABLE_NAME,
   SEARCH_CONDITION,
   R_OWNER,
   R_CONSTRAINT_NAME,
   DELETE_RULE,
   STATUS,
   DEFERRABLE,
   DEFERRED,
   VALIDATED,
   GENERATED,
   BAD,
   RELY,
   LAST_CHANGE,
   INDEX_OWNER,
   INDEX_NAME,
   INVALID,
   VIEW_RELATED
)
AS
   SELECT ou.name,
          oc.name,
          DECODE (c.type#,
                  1, 'C',
                  2, 'P',
                  3, 'U',
                  4, 'R',
                  5, 'V',
                  6, 'O',
                  7, 'C',
                  8, 'H',
                  9, 'F',
                  10, 'F',
                  11, 'F',
                  13, 'F',
                  '?'),
          o.name,
          c.condition,
          ru.name,
          rc.name,
          DECODE (c.type#,
                  4, DECODE (c.refact,  1, 'CASCADE',  2, 'SET NULL',  'NO ACTION'),
                  NULL),
          DECODE (c.type#, 5, 'ENABLED', DECODE (c.enabled, NULL, 'DISABLED', 'ENABLED')),
          DECODE (BITAND (c.defer, 1), 1, 'DEFERRABLE', 'NOT DEFERRABLE'),
          DECODE (BITAND (c.defer, 2), 2, 'DEFERRED', 'IMMEDIATE'),
          DECODE (BITAND (c.defer, 4), 4, 'VALIDATED', 'NOT VALIDATED'),
          DECODE (BITAND (c.defer, 8), 8, 'GENERATED NAME', 'USER NAME'),
          DECODE (BITAND (c.defer, 16), 16, 'BAD', NULL),
          DECODE (BITAND (c.defer, 32), 32, 'RELY', NULL),
          c.mtime,
          DECODE (c.type#,  2, ui.name,  3, ui.name,  NULL),
          DECODE (c.type#,  2, oi.name,  3, oi.name,  NULL),
          DECODE (BITAND (c.defer, 256),
                  256, DECODE (c.type#,
                               4, CASE
                                     WHEN (   BITAND (c.defer, 128) = 128
                                           OR o.status IN (3, 5)
                                           OR ro.status IN (3, 5))
                                     THEN
                                        'INVALID'
                                     ELSE
                                        NULL
                                  END,
                               CASE
                                  WHEN (   BITAND (c.defer, 128) = 128
                                        OR o.status IN (3, 5))
                                  THEN
                                     'INVALID'
                                  ELSE
                                     NULL
                               END),
                  NULL),
          DECODE (BITAND (c.defer, 256), 256, 'DEPEND ON VIEW', NULL)
     FROM sys.con$                    oc,
          sys.con$                    rc,
          sys."_BASE_USER"            ou,
          sys."_BASE_USER"            ru,
          sys."_CURRENT_EDITION_OBJ"  ro,
          sys."_CURRENT_EDITION_OBJ"  o,
          sys.cdef$                   c,
          sys.obj$                    oi,
          sys.user$                   ui
    WHERE     oc.owner# = ou.user#
          AND oc.con# = c.con#
          AND c.obj# = o.obj#
          AND c.type# != 8
          AND (   c.type# < 14
               OR c.type# > 17)                         /* don't include supplog cons   */
          AND (c.type# != 12)                           /* don't include log group cons */
          AND c.rcon# = rc.con#(+)
          AND c.enabled = oi.obj#(+)
          AND oi.owner# = ui.user#(+)
          AND rc.owner# = ru.user#(+)
          AND c.robj# = ro.obj#(+)
          AND (   o.owner# = USERENV ('SCHEMAID')
               OR o.obj# IN (SELECT obj#
                               FROM sys.objauth$
                              WHERE grantee# IN (SELECT kzsrorol FROM x$kzsro))
               OR                                         /* user has system privileges */
                  EXISTS
                     (SELECT NULL
                        FROM v$enabledprivs
                       WHERE priv_number IN (-45                      /* LOCK ANY TABLE */
                                                ,
                                             -47                    /* SELECT ANY TABLE */
                                                ,
                                             -48                    /* INSERT ANY TABLE */
                                                ,
                                             -49                    /* UPDATE ANY TABLE */
                                                ,
                                             -50                    /* DELETE ANY TABLE */
                                                )));

COMMENT ON TABLE SYS.ALL_CONSTRAINTS IS 'Constraint definitions on accessible tables';

COMMENT ON COLUMN SYS.ALL_CONSTRAINTS.CONSTRAINT_TYPE IS 'Type of constraint definition';

você verá que constraint_typeé simplesmente decodificado como

          DECODE (c.type#,
                  1, 'C',
                  2, 'P',
                  3, 'U',
                  4, 'R',
                  5, 'V',
                  6, 'O',
                  7, 'C',
                  8, 'H',
                  9, 'F',
                  10, 'F',
                  11, 'F',
                  13, 'F',
                  '?'),

Se houvesse uma tabela de consulta, ela provavelmente teria sido usada na FROMcláusula e unida a outras tabelas.

Não há tabela de consulta. O Oracle em muitos casos apenas usou strings como você vê em ALL_CONSTRAINTS.CONSTRAINT_TYPE. Eles usam Y/Nem vez de 0/1, etc.