VIEW: SYS.DBA_ROLLING_STATUS

Source

View Text - Preformatted

select s.revision,
         decode(s.status, 0, 'NO PLAN', 1, 'INITIALIZING', 2, 'CONFIGURING',
                          3, 'BUILDING', 4, 'RUNNING', 5, 'READY',
                          6, 'ERROR', 'UNKNOWN') status,
         decode(s.phase, 1, 'NONE', 2, 'INIT', 3, 'BUILD PENDING',
                         4, 'BUILD', 5, 'START PENDING', 6, 'START',
                         7, 'SWITCH PENDING', 8, 'SWITCH',
                         9, 'FINISH PENDING', 10, 'FINISH', 11, 'ROLLBACK',
                         12, 'DONE', 13, 'DESTROY', 'UNKNOWN') phase,
         (select greatest(
            (select nvl(min(instid), 0) from system.rolling$plan
               where revision = (select revision from system.rolling$status)
                 and status != 2 and status != 4
                 and batchid = (select min(batchid) from system.rolling$plan
                                  where revision = (select revision
                                                    from system.rolling$status)
                                    and status != 2 and status != 4)) ,
            (select nvl(max(instid)+1, 0) from system.rolling$plan
               where revision = (select revision from system.rolling$status)
                 and not exists (select 1 from system.rolling$plan
                                  where revision = (select revision
                                                    from system.rolling$status)
                                    and status != 2 and status != 4)))
           from sys.dual)  next_instruction,
         (select count(instid) from system.rolling$plan p
            where p.revision = (select revision from system.rolling$status)
              and (p.status = 1 or p.status = 3)) remaining_instructions,
         s.instance coordinator_instance, s.pid coordinator_pid,
         d.dbun original_primary, d2.dbun future_primary,
         s.dbtotal total_databases, s.dbactive participating_databases,
         s.init_time, s.build_time, s.start_time, s.switch_time, s.finish_time
       from system.rolling$status s, system.rolling$databases d,
            system.rolling$databases d2
     where s.oprimary = d.rdbid and s.fprimary = d2.rdbid
       and s.revision = d.revision and s.revision = d2.revision

View Text - HTML Formatted

SELECT S.REVISION
, DECODE(S.STATUS
, 0
, 'NO PLAN'
, 1
, 'INITIALIZING'
, 2
, 'CONFIGURING'
, 3
, 'BUILDING'
, 4
, 'RUNNING'
, 5
, 'READY'
, 6
, 'ERROR'
, 'UNKNOWN') STATUS
, DECODE(S.PHASE
, 1
, 'NONE'
, 2
, 'INIT'
, 3
, 'BUILD PENDING'
, 4
, 'BUILD'
, 5
, 'START PENDING'
, 6
, 'START'
, 7
, 'SWITCH PENDING'
, 8
, 'SWITCH'
, 9
, 'FINISH PENDING'
, 10
, 'FINISH'
, 11
, 'ROLLBACK'
, 12
, 'DONE'
, 13
, 'DESTROY'
, 'UNKNOWN') PHASE
, (SELECT GREATEST( (SELECT NVL(MIN(INSTID)
, 0)
FROM SYSTEM.ROLLING$PLAN
WHERE REVISION = (SELECT REVISION
FROM SYSTEM.ROLLING$STATUS)
AND STATUS != 2
AND STATUS != 4
AND BATCHID = (SELECT MIN(BATCHID)
FROM SYSTEM.ROLLING$PLAN
WHERE REVISION = (SELECT REVISION
FROM SYSTEM.ROLLING$STATUS)
AND STATUS != 2
AND STATUS != 4))
, (SELECT NVL(MAX(INSTID)+1
, 0)
FROM SYSTEM.ROLLING$PLAN
WHERE REVISION = (SELECT REVISION
FROM SYSTEM.ROLLING$STATUS)
AND NOT EXISTS (SELECT 1
FROM SYSTEM.ROLLING$PLAN
WHERE REVISION = (SELECT REVISION
FROM SYSTEM.ROLLING$STATUS)
AND STATUS != 2
AND STATUS != 4)))
FROM SYS.DUAL) NEXT_INSTRUCTION
, (SELECT COUNT(INSTID)
FROM SYSTEM.ROLLING$PLAN P
WHERE P.REVISION = (SELECT REVISION
FROM SYSTEM.ROLLING$STATUS)
AND (P.STATUS = 1 OR P.STATUS = 3)) REMAINING_INSTRUCTIONS
, S.INSTANCE COORDINATOR_INSTANCE
, S.PID COORDINATOR_PID
, D.DBUN ORIGINAL_PRIMARY
, D2.DBUN FUTURE_PRIMARY
, S.DBTOTAL TOTAL_DATABASES
, S.DBACTIVE PARTICIPATING_DATABASES
, S.INIT_TIME
, S.BUILD_TIME
, S.START_TIME
, S.SWITCH_TIME
, S.FINISH_TIME
FROM SYSTEM.ROLLING$STATUS S
, SYSTEM.ROLLING$DATABASES D
, SYSTEM.ROLLING$DATABASES D2
WHERE S.OPRIMARY = D.RDBID
AND S.FPRIMARY = D2.RDBID
AND S.REVISION = D.REVISION
AND S.REVISION = D2.REVISION