1  select empno,LPAD (' ', LEVEL * 2, ' ')||ename tree,mgr,level,ename
  2  from emp e
  3  --where level<=1 and level<=0
  4  start with mgr --is null
  5  in (7839)
  6  connect by NOCYCLE e.mgr = prior e.empno
  7* order siblings by ename
SQL> /

     EMPNO TREE                        MGR      LEVEL ENAME
---------- -------------------- ---------- ---------- ---------------------
      7698   BLAKE                    7839          1 BLAKE
      7499     ALLEN                  7698          2 ALLEN
      7900     JAMES                  7698          2 JAMES
      7654     MARTIN                 7698          2 MARTIN
      7844     TURNER                 7698          2 TURNER
      7521     WARD                   7698          2 WARD
      7782   CLARK                    7839          1 CLARK
      7934     MILLER                 7782          2 MILLER
      7566   JONES                    7839          1 JONES
      7902     FORD                   7566          2 FORD
      7369       SMITH                7902          3 SMITH
      7788     SCOTT                  7566          2 SCOTT
      7876       ADAMS                7788          3 ADAMS

13 rows selected.

  1  select empno,LPAD (' ', LEVEL * 2, ' ')||ename ename,mgr,level
  2  --,connect_by_isleaf isleaf
  3  --,CONNECT_BY_ISCYCLE  iscycle
  4  from emp e
  5  where level<=1 and level<=0
  6  start with mgr is null
  7* connect by NOCYCLE e.mgr = prior e.empno
SQL> /

no rows selected

  1  select empno,LPAD (' ', LEVEL * 2, ' ')||ename ename,mgr,level
  2  --,connect_by_isleaf isleaf
  3  --,CONNECT_BY_ISCYCLE  iscycle
  4  from emp e
  5  where level<=1 and level<=0
  6  start with mgr is null
  7* connect by NOCYCLE e.mgr = prior e.empno
SQL> /

no rows selected

  1  select empno,LPAD (' ', LEVEL * 2, ' ')||ename tree,mgr,level,ename
  2  from emp e, (select empno r from emp where mgr=7839) rr
  3  where  e.empno=rr.r(+)
  4  and level<=1
  5  --and level<=0
  6  start with r is not null --(7839)
  7  connect by NOCYCLE e.mgr = prior e.empno
  8* order siblings by ename
SQL> /

     EMPNO TREE                        MGR      LEVEL ENAME
---------- -------------------- ---------- ---------- ----------------------------------------
      7698   BLAKE                    7839          1 BLAKE
      7782   CLARK                    7839          1 CLARK
      7566   JONES                    7839          1 JONES

SQL> ed
Wrote file afiedt.buf

  1  select empno,LPAD (' ', LEVEL * 2, ' ')||ename tree,mgr,level,ename
  2  from emp e, (select empno r from emp where mgr=7839) rr
  3  where  e.empno=rr.r(+)
  4  and level<=1 and level<=0
  5  start with r is not null --(7839)
  6  connect by NOCYCLE e.mgr = prior e.empno
  7* order siblings by ename
SQL> /

no rows selected

SQL> select empno,LPAD (' ', LEVEL * 2, ' ')||ename tree,mgr,level,ename
  2  from emp e, (select empno r from emp where mgr=7839) rr
  3  where  e.empno=rr.r(+)
  4  and level<=1
  5  --and level<=0
  6  start with r is not null --(7839)
  7  connect by NOCYCLE e.mgr = prior e.empno
  8  order siblings by ename
  9  /

     EMPNO TREE                        MGR      LEVEL ENAME
---------- -------------------- ---------- ---------- ----------------------
      7698   BLAKE                    7839          1 BLAKE
      7782   CLARK                    7839          1 CLARK
      7566   JONES                    7839          1 JONES

SQL> ed
Wrote file afiedt.buf

  1  select empno,LPAD (' ', LEVEL * 2, ' ')||ename tree,mgr,level,ename
  2  from emp e, (select empno r from emp where mgr=7839) rr
  3  where  e.empno=rr.r(+)
  4  and level<=1
  5  and level<=0
  6  start with r is not null --(7839)
  7  connect by NOCYCLE e.mgr = prior e.empno
  8* order siblings by ename
SQL> /

no rows selected

Невозможно обойти дерево в ширину с помощью connect by.
Можно rec with или моделью для любителей экзотики.

  1  select empno,LPAD (' ', LEVEL * 2, ' ')||ename tree,mgr,level
  2  from emp e
  3  start with mgr is null
  4  connect by NOCYCLE e.mgr = prior e.empno
  5* order by level,ename
SQL> /

     EMPNO TREE                        MGR      LEVEL
---------- -------------------- ---------- ----------
      7839   KING                                   1
      7698     BLAKE                  7839          2
      7782     CLARK                  7839          2
      7566     JONES                  7839          2
      7499       ALLEN                7698          3
      7902       FORD                 7566          3
      7900       JAMES                7698          3
      7654       MARTIN               7698          3
      7934       MILLER               7782          3
      7788       SCOTT                7566          3
      7844       TURNER               7698          3
      7521       WARD                 7698          3
      7876         ADAMS              7788          4
      7369         SMITH              7902          4

14 rows selected.

create or replace function stax_foto(p_p varchar2,p_l int :=0,p_r int :=0)
return varchar2
is
  v varchar2(4000);
begin
 if p_l=1 and p_r=1 then 
   DBMS_APPLICATION_INFO.SET_CLIENT_INFO('');
 end if;
 v:=userenv('client_info')||'<'||p_p||'/'||p_l||'.'||p_r||'> ';
 DBMS_APPLICATION_INFO.SET_CLIENT_INFO(v);
 return v;
end;
/

with tree as (
select 2 id, 1 id_parent from dual union all
select 3 id, 1 id_parent from dual union all
select 4 id, 3 id_parent from dual union all
select 5 id, 4 id_parent from dual union all
select 11 id, 10 id_parent from dual union all
select 12 id, 11 id_parent from dual union all
select 13 id, 11 id_parent from dual )
, rec(lvl, id, id_parent, grand_parent, p) as (
 select
   1 lvl, id, id_parent,cast(null as number)
-- ,'<'||id||'/1.'||rownum||'> '
  ,stax_foto(id,1,rownum)
 from tree where id_parent in (1, 10)
 union all
 select r.lvl + 1, t.id, t.id_parent, r.id_parent
--       ,r.p||'<'||t.id||'/'||(r.lvl + 1)||'.'||rownum||'> '
 ,stax_foto(t.id,r.lvl+1,rownum)
from
 tree t join rec r on t.id_parent = r.id
)
--search depth first by id set ord
select
  lvl
 ,rpad(' ', (lvl - 1) * 3, ' ') || id as id, id_parent, grand_parent
 ,p
from rec
/

SQL> /

       LVL ID          ID_PARENT GRAND_PARENT P
---------- ---------- ---------- ------------ ----------------------------------------------------------------
         1 2                   1              <2/1.1>
         1 3                   1              <2/1.1> <3/1.2>
         1 11                 10              <2/1.1> <3/1.2> <11/1.3>
         2    4                3            1 <2/1.1> <3/1.2> <11/1.3> <4/2.1>
         2    13              11           10 <2/1.1> <3/1.2> <11/1.3> <4/2.1> <13/2.2>
         2    12              11           10 <2/1.1> <3/1.2> <11/1.3> <4/2.1> <13/2.2> <12/2.3>
         3       5             4            3 <2/1.1> <3/1.2> <11/1.3> <4/2.1> <13/2.2> <12/2.3> <5/3.1>

SQL> with tree as (
  2  select 2 id, 1 id_parent from dual union all
  3  select 3 id, 1 id_parent from dual union all
  4  select 4 id, 3 id_parent from dual union all
  5  select 5 id, 4 id_parent from dual union all
  6  select 11 id, 10 id_parent from dual union all
  7  select 12 id, 11 id_parent from dual union all
  8  select 13 id, 11 id_parent from dual )
  9  , rec(lvl, id, id_parent, grand_parent, p) as (
 10   select
 11     1 lvl, id, id_parent,cast(null as number)
 12  -- ,'<'||id||'/1.'||rownum||'> '
 13    ,stax_foto(id,1,rownum)
 14   from tree where id_parent in (1, 10)
 15   union all
 16   select r.lvl + 1, t.id, t.id_parent, r.id_parent
 17  --       ,r.p||'<'||t.id||'/'||(r.lvl + 1)||'.'||rownum||'> '
 18   ,stax_foto(t.id,r.lvl+1,rownum)
 19  from
 20   tree t join rec r on t.id_parent = r.id
 21  )
 22  search depth first by id set ord
 23  select
 24    lvl
 25   ,rpad(' ', (lvl - 1) * 3, ' ') || id as id, id_parent, grand_parent
 26   ,p
 27  from rec
 28  /

       LVL ID          ID_PARENT GRAND_PARENT P
---------- ---------- ---------- ------------ ---------------------------------------------------------------
         1 2                   1              <2/1.1>
         1 3                   1              <2/1.1> <3/1.2>
         2    4                3            1 <2/1.1> <3/1.2> <11/1.3> <4/2.1>
         3       5             4            3 <2/1.1> <3/1.2> <11/1.3> <4/2.1> <13/2.2> <12/2.3> <5/3.1>
         1 11                 10              <2/1.1> <3/1.2> <11/1.3>
         2    12              11           10 <2/1.1> <3/1.2> <11/1.3> <4/2.1> <13/2.2> <12/2.3>
         2    13              11           10 <2/1.1> <3/1.2> <11/1.3> <4/2.1> <13/2.2>

7 rows selected.

with tree as (
select 3 id, 1 id_parent from dual union all
select 4 id, 3 id_parent from dual union all
select 5 id, 4 id_parent from dual union all
select 2 id, 1 id_parent from dual union all
select 11 id, 10 id_parent from dual union all
select 12 id, 11 id_parent from dual union all
select 13 id, 11 id_parent from dual )
select level lvl,rpad(' ', (level - 1) * 3, ' ') || id as id_txt,id_parent
  ,stax_foto(id,1,rownum) p
 from tree
 start with id_parent in (1, 10)
 connect by id_parent=prior id
order siblings by id
/
SQL> /

       LVL ID_TXT      ID_PARENT P
---------- ---------- ---------- ------------------------------------------------------------
         1 2                   1 <2/1.1>
         1 3                   1 <2/1.1> <3/1.2>
         2    4                3 <2/1.1> <3/1.2> <4/1.3>
         3       5             4 <2/1.1> <3/1.2> <4/1.3> <5/1.4>
         1 11                 10 <2/1.1> <3/1.2> <4/1.3> <5/1.4> <11/1.5>
         2    12              11 <2/1.1> <3/1.2> <4/1.3> <5/1.4> <11/1.5> <12/1.6>
         2    13              11 <2/1.1> <3/1.2> <4/1.3> <5/1.4> <11/1.5> <12/1.6> <13/1.7>

7 rows selected.

select empno,LPAD (' ', LEVEL * 2, ' ')||ename tree,mgr,level,ename
from emp e, (select empno r from emp where mgr=7839) rr
where  e.empno=rr.r(+)
--and level<=1
--and level<=0
start with r is not null --(7839)
connect by NOCYCLE e.mgr = prior e.empno
and level<=1
and level<=0
and 1=0
order siblings by ename