VIEW: SYS.DBA_LOGSTDBY_PROGRESS

Source

View Text - Preformatted

select
    applied_scn,
    /* thread# derived from applied_scn */
    (select min(thread#) from logstdby_log
     where sequence# =
       (select max(sequence#) from logstdby_log l
        where applied_scn >= first_change# and applied_scn <= next_change#)
    and applied_scn >= first_change#
    and applied_scn <= next_change#)
       applied_thread#,
    /* sequence# derived from applied_scn */
    (select max(sequence#) from logstdby_log l
     where applied_scn >= first_change# and applied_scn <= next_change#)
       applied_sequence#,
    /* estimated time derived from applied_scn */
    (select max(first_time +
        ((next_time - first_time) / (next_change# - first_change#) *
         (applied_scn - first_change#)))
     from logstdby_log l
     where applied_scn >= first_change# and applied_scn <= next_change#)
       applied_time,
    read_scn,
    /* thread# derived from read_scn */
    (select min(thread#) from logstdby_log
     where sequence# =
       (select max(sequence#) from logstdby_log l
        where read_scn >= first_change# and read_scn <= next_change#)
     and read_scn >= first_change#
     and read_scn <= next_change#)
       read_thread#,
    /* sequence# derived from read_scn */
    (select max(sequence#) from logstdby_log l
     where read_scn >= first_change# and read_scn <= next_change#)
       read_sequence#,
    /* estimated time derived from read_scn */
    (select min(first_time +
        ((next_time - first_time) / (next_change# - first_change#) *
         (read_scn - first_change#)))
     from logstdby_log l
     where read_scn >= first_change# and read_scn <= next_change#)
       read_time,
    newest_scn,
    /* thread# derived from newest_scn */
    (select min(thread#) from logstdby_log
     where sequence# =
       (select max(sequence#) from logstdby_log l
        where newest_scn >= first_change# and newest_scn <= next_change#)
     and newest_scn >= first_change#
     and newest_scn <= next_change#)
       newest_thread#,
    /* sequence# derived from newest_scn */
    (select max(sequence#) from logstdby_log l
     where newest_scn >= first_change# and newest_scn <= next_change#)
       newest_sequence#,
    /* estimated time derived from newest_scn */
    (select max(first_time +
        ((next_time - first_time) / (next_change# - first_change#) *
         (newest_scn - first_change#)))
     from logstdby_log l
     where newest_scn >= first_change# and newest_scn <= next_change#)
       newest_time
  from
    /* in-line view to calculate relavent scn values */
    (select /* APPLIED_SCN */
            greatest(nvl((select max(a.processed_scn) - 1
                          from system.logstdby$apply_milestone a),0),
                     nvl((select max(a.commit_scn)
                          from system.logstdby$apply_milestone a),0),
                     sx.start_scn) applied_scn,
            /* READ_SCN */
            greatest(nvl(sx.spill_scn,1), sx.start_scn) read_scn,
            /* NEWEST_SCN */
            nvl((select max(next_change#)-1 from logstdby_log),
                sx.start_scn) newest_scn
    from system.logmnr_session$ sx
    where sx.session# =
      (select value from system.logstdby$parameters where name = 'LMNR_SID')) x

View Text - HTML Formatted

SELECT APPLIED_SCN
, /* THREAD# DERIVED
FROM APPLIED_SCN */ (SELECT MIN(THREAD#)
FROM LOGSTDBY_LOG
WHERE SEQUENCE# = (SELECT MAX(SEQUENCE#)
FROM LOGSTDBY_LOG L
WHERE APPLIED_SCN >= FIRST_CHANGE#
AND APPLIED_SCN <= NEXT_CHANGE#)
AND APPLIED_SCN >= FIRST_CHANGE#
AND APPLIED_SCN <= NEXT_CHANGE#) APPLIED_THREAD#
, /* SEQUENCE# DERIVED
FROM APPLIED_SCN */ (SELECT MAX(SEQUENCE#)
FROM LOGSTDBY_LOG L
WHERE APPLIED_SCN >= FIRST_CHANGE#
AND APPLIED_SCN <= NEXT_CHANGE#) APPLIED_SEQUENCE#
, /* ESTIMATED TIME DERIVED
FROM APPLIED_SCN */ (SELECT MAX(FIRST_TIME + ((NEXT_TIME - FIRST_TIME) / (NEXT_CHANGE# - FIRST_CHANGE#) * (APPLIED_SCN - FIRST_CHANGE#)))
FROM LOGSTDBY_LOG L
WHERE APPLIED_SCN >= FIRST_CHANGE#
AND APPLIED_SCN <= NEXT_CHANGE#) APPLIED_TIME
, READ_SCN
, /* THREAD# DERIVED
FROM READ_SCN */ (SELECT MIN(THREAD#)
FROM LOGSTDBY_LOG
WHERE SEQUENCE# = (SELECT MAX(SEQUENCE#)
FROM LOGSTDBY_LOG L
WHERE READ_SCN >= FIRST_CHANGE#
AND READ_SCN <= NEXT_CHANGE#)
AND READ_SCN >= FIRST_CHANGE#
AND READ_SCN <= NEXT_CHANGE#) READ_THREAD#
, /* SEQUENCE# DERIVED
FROM READ_SCN */ (SELECT MAX(SEQUENCE#)
FROM LOGSTDBY_LOG L
WHERE READ_SCN >= FIRST_CHANGE#
AND READ_SCN <= NEXT_CHANGE#) READ_SEQUENCE#
, /* ESTIMATED TIME DERIVED
FROM READ_SCN */ (SELECT MIN(FIRST_TIME + ((NEXT_TIME - FIRST_TIME) / (NEXT_CHANGE# - FIRST_CHANGE#) * (READ_SCN - FIRST_CHANGE#)))
FROM LOGSTDBY_LOG L
WHERE READ_SCN >= FIRST_CHANGE#
AND READ_SCN <= NEXT_CHANGE#) READ_TIME
, NEWEST_SCN
, /* THREAD# DERIVED
FROM NEWEST_SCN */ (SELECT MIN(THREAD#)
FROM LOGSTDBY_LOG
WHERE SEQUENCE# = (SELECT MAX(SEQUENCE#)
FROM LOGSTDBY_LOG L
WHERE NEWEST_SCN >= FIRST_CHANGE#
AND NEWEST_SCN <= NEXT_CHANGE#)
AND NEWEST_SCN >= FIRST_CHANGE#
AND NEWEST_SCN <= NEXT_CHANGE#) NEWEST_THREAD#
, /* SEQUENCE# DERIVED
FROM NEWEST_SCN */ (SELECT MAX(SEQUENCE#)
FROM LOGSTDBY_LOG L
WHERE NEWEST_SCN >= FIRST_CHANGE#
AND NEWEST_SCN <= NEXT_CHANGE#) NEWEST_SEQUENCE#
, /* ESTIMATED TIME DERIVED
FROM NEWEST_SCN */ (SELECT MAX(FIRST_TIME + ((NEXT_TIME - FIRST_TIME) / (NEXT_CHANGE# - FIRST_CHANGE#) * (NEWEST_SCN - FIRST_CHANGE#)))
FROM LOGSTDBY_LOG L
WHERE NEWEST_SCN >= FIRST_CHANGE#
AND NEWEST_SCN <= NEXT_CHANGE#) NEWEST_TIME FROM /* IN-LINE VIEW TO CALCULATE RELAVENT SCN VALUES */ (SELECT /* APPLIED_SCN */ GREATEST(NVL((SELECT MAX(A.PROCESSED_SCN) - 1
FROM SYSTEM.LOGSTDBY$APPLY_MILESTONE A)
, 0)
, NVL((SELECT MAX(A.COMMIT_SCN)
FROM SYSTEM.LOGSTDBY$APPLY_MILESTONE A)
, 0)
, SX.START_SCN) APPLIED_SCN
, /* READ_SCN */ GREATEST(NVL(SX.SPILL_SCN
, 1)
, SX.START_SCN) READ_SCN
, /* NEWEST_SCN */ NVL((SELECT MAX(NEXT_CHANGE#)-1
FROM LOGSTDBY_LOG)
, SX.START_SCN) NEWEST_SCN
FROM SYSTEM.LOGMNR_SESSION$ SX
WHERE SX.SESSION# = (SELECT VALUE
FROM SYSTEM.LOGSTDBY$PARAMETERS
WHERE NAME = 'LMNR_SID')) X