Let's start with current incarnation as root node, then work our way down the older incarnations:
SELECT INCARNATION#, LPAD(' ', LEVEL*2) || STATUS "STATUS", RESETLOGS_TIME,RESETLOGS_CHANGE#,PRIOR_INCARNATION#
FROM V$DATABASE_INCARNATION
START WITH INCARNATION# = 4
CONNECT BY PRIOR PRIOR_INCARNATION# = INCARNATION#;
INCARNATION# STATUS RESETLOGS_ RESETLOGS_CHANGE# PRIOR_INCARNATION#
------------ -------------------- ---------- ----------------- ------------------
4 CURRENT 30.06.2014 136759423847 3
3 PARENT 07.05.2013 130807968161 2
2 PARENT 14.02.2011 1002970 1
1 PARENT 07.10.2010 1 0
The lineCONNECT BY PRIOR PRIOR_INCARNATION# = INCARNATION#
indicate where the recursion starts, and should be should be read as
"print the rows that has INCARNATION# equal to the previous row's PRIOR_INCARNATION#"
In the reverse order, starting with the oldest incarnation, and working our way through the incarnation list:
SELECT INCARNATION#,LPAD(' ', LEVEL*2) || STATUS "STATUS" , RESETLOGS_TIME, RESETLOGS_CHANGE#,PRIOR_INCARNATION#
FROM V$DATABASE_INCARNATION
START WITH INCARNATION# = 1
CONNECT BY PRIOR_INCARNATION# = PRIOR INCARNATION#;
INCARNATION# STATUS RESETLOGS_ RESETLOGS_CHANGE# PRIOR_INCARNATION#
------------ -------------------- ---------- ----------------- ------------------
1 PARENT 07.10.2010 1 0
2 PARENT 14.02.2011 1002970 1
3 PARENT 07.05.2013 130807968161 2
4 CURRENT 30.06.2014 136759423847 3
Here, the line
CONNECT BY PRIOR_INCARNATION# = PRIOR INCARNATION#
should be read as
"print the next rows that has its PRIOR_INCARNATION# equal to the previous row's INCARNATION#"
I find the semantics of the recursion to be important; when you understand what you're actually selecting, it's so much easier to construct the SQL statement and get it right the first time around.
