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 0The line
CONNECT 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.
No comments:
Post a Comment