Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE table_name (Child, Parent) AS
SELECT 1, null FROM DUAL UNION ALL
SELECT 2, 1    FROM DUAL UNION ALL
SELECT 3, 2    FROM DUAL UNION ALL
SELECT 4, null FROM DUAL UNION ALL
SELECT 5, 4    FROM DUAL UNION ALL
SELECT 6, null FROM DUAL UNION ALL
SELECT 7, null FROM DUAL UNION ALL
SELECT 8, 3    FROM DUAL UNION ALL
SELECT 9, 5    FROM DUAL;
9 rows affected

SELECT child,
       CONNECT_BY_ROOT child AS oldest_ancestor
FROM   table_name
START WITH parent IS NULL
CONNECT BY parent = PRIOR child
ORDER BY child;


CHILD
OLDEST_ANCESTOR


1
1

2
1

3
1

4
4

5
4

6
6

7
7

8
1

9
4


WITH rsqfc (child, oldest_ancestor) AS (
  SELECT child, child
  FROM   table_name
  WHERE  parent IS NULL
UNION ALL
  SELECT t.child, r.oldest_ancestor
  FROM   rsqfc r
         INNER JOIN table_name t
         ON (t.parent = r.child)
)
SELECT *
FROM   rsqfc
ORDER BY child;


CHILD
OLDEST_ANCESTOR


1
1

2
1

3
1

4
4

5
4

6
6

7
7

8
1

9
4