ODI Topology Connection Detail (Master Repository)

/* 10g MASTER REPOSITIRY TOPOLOGY MAPPING */
SELECT DISTINCT ODIM.SNP_TECHNO.TECHNO_NAME AS TECHNOLOGY,
                  ODIM.SNP_CONNECT.CON_NAME AS DATA_SERVER,
                  ODIM.SNP_CONNECT.USER_NAME AS USERNAME,
                  ODIM.SNP_CONNECT.PASS AS PASSWD,
                  ODIM.SNP_PSCHEMA.EXT_NAME AS PHYSICAL_SCHEMA,
                  ODIM.SNP_PSCHEMA.SCHEMA_NAME AS SCHEMA_NAME,
                  ODIM.SNP_PSCHEMA.WSCHEMA_NAME AS WORK_SCHEMA,
                  ODIM.SNP_CONTEXT.CONTEXT_NAME AS CONTEXT_NAME,
                  ODIM.SNP_LSCHEMA.LSCHEMA_NAME AS LOGICAL_SCHEMA,
                  ODIM.SNP_CONNECT.JAVA_DRIVER AS DRIVER_INFO,
                  ODIM.SNP_MTXT_PART.TXT AS URL
    FROM ODIM.SNP_TECHNO
         LEFT OUTER JOIN ODIM.SNP_CONNECT ON ODIM.SNP_CONNECT.I_TECHNO = ODIM.SNP_TECHNO.I_TECHNO
         LEFT OUTER JOIN ODIM.SNP_PSCHEMA ON ODIM.SNP_PSCHEMA.I_CONNECT = ODIM.SNP_CONNECT.I_CONNECT
         LEFT OUTER JOIN ODIM.SNP_PSCHEMA_CONT ON ODIM.SNP_PSCHEMA_CONT.I_PSCHEMA = ODIM.SNP_PSCHEMA.I_PSCHEMA
         LEFT OUTER JOIN ODIM.SNP_LSCHEMA ON ODIM.SNP_LSCHEMA.I_LSCHEMA = ODIM.SNP_PSCHEMA_CONT.I_LSCHEMA
         LEFT OUTER JOIN ODIM.SNP_CONTEXT ON ODIM.SNP_CONTEXT.I_CONTEXT = ODIM.SNP_PSCHEMA_CONT.I_CONTEXT
         LEFT OUTER JOIN ODIM.SNP_MTXT_PART ON ODIM.SNP_MTXT_PART.I_TXT = ODIM.SNP_CONNECT.I_TXT_JAVA_URL
   WHERE ODIM.SNP_CONNECT.CON_NAME IS NOT NULL
ORDER BY ODIM.SNP_TECHNO.TECHNO_NAME;

11g
  SELECT DISTINCT
         SNP_TECHNO.TECHNO_NAME AS TECHNOLOGY,
         SNP_CONNECT.CON_NAME AS DATA_SERVER,
         SNP_CONNECT.USER_NAME AS USERNAME,
         SNP_CONNECT.PASS AS PASSWD,

         SNP_PSCHEMA.EXT_NAME AS PHYSICAL_SCHEMA,
         SNP_PSCHEMA.SCHEMA_NAME AS SCHEMA_NAME,
         SNP_PSCHEMA.WSCHEMA_NAME AS WORK_SCHEMA,
         SNP_CONTEXT.CONTEXT_NAME AS CONTEXT_NAME,
         SNP_LSCHEMA.LSCHEMA_NAME AS LOGICAL_SCHEMA,
         SNP_CONNECT.JAVA_DRIVER AS DRIVER_INFO,
         COALESCE (SNP_MTXT_PART.TXT, DBMS_LOB.SUBSTR (SNP_MTXT.FULL_TXT, 1000, 1)) AS URL,
         SNP_CONNECT.USER_NAME,
         SNP_CONNECT.PASS
    FROM SNP_TECHNO
         LEFT OUTER JOIN SNP_CONNECT
            ON SNP_CONNECT.I_TECHNO = SNP_TECHNO.I_TECHNO
         LEFT OUTER JOIN SNP_PSCHEMA
            ON SNP_PSCHEMA.I_CONNECT = SNP_CONNECT.I_CONNECT
         LEFT OUTER JOIN SNP_PSCHEMA_CONT
            ON SNP_PSCHEMA_CONT.I_PSCHEMA = SNP_PSCHEMA.I_PSCHEMA
         LEFT OUTER JOIN SNP_LSCHEMA
            ON SNP_LSCHEMA.I_LSCHEMA = SNP_PSCHEMA_CONT.I_LSCHEMA
         LEFT OUTER JOIN SNP_CONTEXT
            ON SNP_CONTEXT.I_CONTEXT = SNP_PSCHEMA_CONT.I_CONTEXT
         LEFT OUTER JOIN SNP_MTXT_PART
            ON SNP_MTXT_PART.I_TXT = SNP_CONNECT.I_TXT_JAVA_URL
         LEFT OUTER JOIN SNP_MTXT
            ON SNP_MTXT.I_TXT = SNP_CONNECT.I_TXT_JAVA_URL
   WHERE SNP_CONNECT.CON_NAME IS NOT NULL
ORDER BY SNP_TECHNO.TECHNO_NAME;

Reference : http://odiexperts.com/odi-topology-connection-detail/