Untitled

WITH object_types AS
 (SELECT 1 id, NULL pid, '0' NAME FROM dual UNION ALL
  SELECT 2 id, 1 pid, '0.1' NAME FROM dual UNION ALL
  SELECT 3 id, 2 pid, '0.1.1' NAME FROM dual UNION ALL
  SELECT 4 id, 3 pid, '0.1.1.1' NAME FROM dual UNION ALL
  SELECT 5 id, 4 pid, '0.1.1.1.1' NAME FROM dual UNION ALL
  SELECT 6 id, 5 pid, '0.1.1.1.1.1' NAME FROM dual),
attr_object_types AS
 (SELECT 1 id, 1 type_id, 1 schema_id, 0 options FROM dual UNION ALL
  SELECT 2 id, 1 type_id, 1 schema_id, 2 options FROM dual UNION ALL
  SELECT 2 id, 2 type_id, 1 schema_id, 0 options FROM dual UNION ALL -- !!!
  SELECT 3 id, 2 type_id, 1 schema_id, 0 options FROM dual UNION ALL
  SELECT 4 id, 3 type_id, 1 schema_id, 0 options FROM dual UNION ALL
  SELECT 5 id, 3 type_id, 1 schema_id, 0 options FROM dual UNION ALL
  SELECT 6 id, 3 type_id, 1 schema_id, 2 options FROM dual UNION ALL
  SELECT 6 id, 4 type_id, 1 schema_id, 0 options FROM dual UNION ALL -- !!!
  SELECT 6 id, 5 type_id, 1 schema_id, 0 options FROM dual UNION ALL -- !!!

  SELECT 7 id, 4 type_id, 1 schema_id, 0 options FROM dual UNION ALL
  SELECT 8 id, 4 type_id, 1 schema_id, 0 options FROM dual UNION ALL
  SELECT 9 id, 4 type_id, 2 schema_id, 0 options FROM dual UNION ALL
  SELECT 7 id, 5 type_id, 1 schema_id, 0 options FROM dual UNION ALL
  SELECT 10 id, 3 type_id, 2 schema_id, 0 options FROM dual UNION ALL
  SELECT 8 id, 2 type_id, 1 schema_id, 0 options FROM dual),
attrs AS
 (SELECT 1 id, 1 schema_id, 'attr1' NAME FROM dual UNION ALL
  SELECT 2 id, 1 schema_id, 'attr2' NAME FROM dual UNION ALL
  SELECT 3 id, 1 schema_id, 'attr3' NAME FROM dual UNION ALL
  SELECT 4 id, 1 schema_id, 'attr4' NAME FROM dual UNION ALL
  SELECT 5 id, 1 schema_id, 'attr5' NAME FROM dual UNION ALL
  SELECT 6 id, 2 schema_id, 'attr6' NAME FROM dual UNION ALL
  SELECT 7 id, 2 schema_id, 'attr7' NAME FROM dual UNION ALL
  SELECT 8 id, 2 schema_id, 'attr8' NAME FROM dual UNION ALL
  SELECT 9 id, 2 schema_id, 'attr9' NAME FROM dual UNION ALL
  SELECT 10 id, 2 schema_id, 'attr10' NAME FROM dual UNION ALL
  SELECT 2 id, 2 schema_id, 'attr2' NAME FROM dual UNION ALL
  SELECT 3 id, 2 schema_id, 'attr3' NAME FROM dual UNION ALL
  SELECT 8 id, 1 schema_id, 'attr8' NAME FROM dual UNION ALL
  SELECT 7 id, 1 schema_id, 'attr7' NAME FROM dual UNION ALL
  SELECT 6 id, 1 schema_id, 'attr6' NAME FROM dual),
ot AS
 (SELECT ot.id,
         ot.pid,
         lpad(' ', 4 * (MAX(LEVEL) over(ORDER BY NAME) - LEVEL)) || ot.name NAME,
         (MAX(LEVEL) over(ORDER BY NAME) - LEVEL) lvl
    FROM object_types ot
   START WITH ot.id = 6
  CONNECT BY PRIOR ot.pid = id),
att AS
(SELECT aot0.*, a0.name
  FROM attr_object_types aot0, attrs a0
 WHERE aot0.id = a0.id
   AND aot0.schema_id = a0.schema_id
)

SELECT *
  FROM ot o1, att a1
 WHERE a1.type_id = o1.id
   AND a1.schema_id = 1
   AND NOT EXISTS (SELECT 1
          FROM ot o2, att a2
         WHERE a2.type_id = o2.id
           AND o2.lvl < o1.lvl
           AND a2.schema_id = a1.schema_id
           AND a2.id = a1.id
           AND a2.options = 2)
   AND a1.options != 2;