Tuesday, July 1, 2014

How to print the database incarnations from V$DATABASE_INCARNATION using an hierarchical query

I just realized that V$DATABASE_INCARNATION is a perfect candidate for using hierarchical queries for formatting.

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 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