Oracle 11g Release 2

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

BANNER
Oracle Database 11g Express Edition Release 11.2.0.2.0 - 64bit Production
PL/SQL Release 11.2.0.2.0 - Production
CORE 11.2.0.2.0 Production
TNS for Linux: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

with my_tree as (
   select 'level 4.1' node, 'level 3.1' parent_node from dual union
   select 'level 4.2' node, 'level 3.2' parent_node from dual union
   select 'level 3.1' node, 'level 2'   parent_node from dual union
   select 'level 3.2' node, 'level 2'   parent_node from dual union
   select 'level 2'   node, 'level 1'   parent_node from dual union
   select 'level 1'   node, ''          parent_node from dual
)
select level, t.node, t.parent_node, prior t.node child_that_pointed_me_here
       ,sys_connect_by_path(t.node,'/') as lineage
       ,instr(reverse(sys_connect_by_path(t.node,'/')),'/',1,1)
       ,reverse(
         substr(
               reverse(sys_connect_by_path(t.node,'/'))
               ,instr(reverse(sys_connect_by_path(t.node,'/')),'/',1,2)
               ,instr(reverse(sys_connect_by_path(t.node,'/')),'/',1,3)
               -
               instr(reverse(sys_connect_by_path(t.node,'/')),'/',1,2)
               )
              )as reverse
  from my_tree t
connect by t.node = prior t.parent_node
start with t.node like 'level 4%'

LEVEL	NODE	PARENT_NODE	CHILD_THAT_POINTED_ME_HERE	LINEAGE	INSTR(REVERSE(SYS_CONNECT_BY_PATH(T.NODE,'/')),'/',1,1)	REVERSE
1	level 4.1	level 3.1	null	/level 4.1	10	null
2	level 3.1	level 2	level 4.1	/level 4.1/level 3.1	10	null
3	level 2	level 1	level 3.1	/level 4.1/level 3.1/level 2	8	level 4.1/
4	level 1	null	level 2	/level 4.1/level 3.1/level 2/level 1	8	level 3.1/
1	level 4.2	level 3.2	null	/level 4.2	10	null
2	level 3.2	level 2	level 4.2	/level 4.2/level 3.2	10	null
3	level 2	level 1	level 3.2	/level 4.2/level 3.2/level 2	8	level 4.2/
4	level 1	null	level 2	/level 4.2/level 3.2/level 2/level 1	8	level 3.2/