V$RMAN_STATUS definition in Oracle Database 11.2
V$RMAN_STATUS

SELECT
  nvl(R1.SID,0),                           -- SID NUMBER
  nvl(R1.RECID,R2.RSRRID),                 -- RECID NUMBER
  nvl(R1.STAMP,R2.RSRTST),                 -- STAMP NUMBER
  decode(                                  -- PARENT_RECID NUMBER
    nvl(R1.ROW_LEVEL, R2.RSRLV), 0, to_number(NULL),nvl(R1.PARENT_RECID, RSRPI)),
  decode(                                  -- PARENT_STAMP NUMBER
    nvl(R1.ROW_LEVEL, R2.RSRLV), 0, to_number(NULL),nvl(R1.PARENT_STAMP, R2.RSRPS)),
  decode (                                 -- SESSION_RECID NUMBER
    nvl(R1.ROW_LEVEL, R2.RSRLV), 0, nvl(R1.RECID,R2.RSRRID), R2.RSR0I),
  decode (                                 -- SESSION_STAMP NUMBER
    nvl(R1.ROW_LEVEL, R2.RSRLV), 0, nvl(R1.STAMP,R2.RSRTST), R2.RSR0S),
  nvl(R1.ROW_LEVEL, R2.RSRLV),             -- ROW_LEVEL NUMBER
  nvl(R1.ROW_TYPE, decode(R2.RSRLV, 0, 'SESSION',1, 'COMMAND','RECURSIVE OPERATION')),-- ROW_TYPE VARCHAR2(19)
  nvl(R1.COMMAND_ID,R2.RSRCI),             -- COMMAND_ID VARCHAR2(33)
  nvl(R1.OPERATION, UPPER(R2.RSROP)),      -- OPERATION VARCHAR2(33)
  nvl(R1.STATUS, decode(bitand(R2.RSRIS,2+1), 2,decode(R2.RSRES, 1, 'RUNNING',1+8, 'RUNNING WITH WARNINGS', 1+16, 'RUNNING WITH ERRORS', 1+8+16, 'RUNNING WITH ERRORS', 2, 'COMPLETED', 2+8, 'COMPLETED WITH WARNINGS', 2+16, 'COMPLETED WITH ERRORS', 2+8+16, 'COMPLETED WITH ERRORS', 'FAILED'), 'FAILED')),-- STATUS VARCHAR2(23)
  decode(                                  -- MBYTES_PROCESSED NUMBER
    R2.RSRMP, 0, nvl(HH.MBYTES, 0 ),R2.RSRMP),
  nvl(R1.START_TIME, to_date(R2.RSRST,'MM/DD/RR HH24:MI:SS', 'NLS_CALENDAR=Gregorian')),-- START_TIME DATE
  nvl(R1.END_TIME, to_date(R2.RSRET,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian')),-- END_TIME DATE
  decode(                                  -- INPUT_BYTES NUMBER
    R2.RSRIM, 0, nvl(HH.INPBYTES, 0), decode(bitand(R2.RSRIM, 2147483648), 0,R2.RSRIM, 2147483648, bitand(R2.RSRIM,2147483647)*1024*1024)),
  decode(                                  -- OUTPUT_BYTES NUMBER
    R2.RSROM, 0, nvl(HH.OUTBYTES, 0), decode(bitand(R2.RSROM, 2147483648), 0,R2.RSROM, 2147483648, bitand(R2.RSROM,2147483647)*1024*1024)),
  decode(                                  -- OPTIMIZED VARCHAR2(3)
    bitand(R2.RSRFL, 32), 1, 'YES', 'NO'),
  nvl(nvl(nvl(nvl(nvl(nvl(nvl( decode(bitand(R2.RSRFL, 1+128),1, 'DB FULL',null), decode(bitand(R2.RSRFL, 64), 64, 'RECVR AREA', null)), decode(bitand(R2.RSRFL, 1+128), 129, 'DB INCR', null)), decode(bitand(R2.RSRFL, 2+128),2, 'DATAFILE FULL', 130, 'DATAFILE INCR', null)), decode(bitand(R2.RSRFL, 4), 4, 'ARCHIVELOG', null)), decode(bitand(R2.RSRFL, 8), 8, 'CONTROLFILE', null)), decode(bitand(R2.RSRFL, 16), 16, 'SPFILE', null)), decode(bitand(R2.RSRFL, 256), 256, 'BACKUPSET', null)),-- OBJECT_TYPE VARCHAR2(13)
  decode(                                  -- OUTPUT_DEVICE_TYPE VARCHAR2(17)
    bitand(R2.RSRIS, 32+16+8+4), 0, odev.device_type, 4, 'DISK', 8, 'SBT_TAPE', 16, '*', null) device_type,
  decode(                                  -- OSB_ALLOCATED VARCHAR2(3)
    bitand(R2.RSRFL, 512), 512, 'YES', 'NO') OSB 
FROM
  X$KCCRSR R2,
  GV$RMAN_STATUS_CURRENT R1,
  (
  SELECT
    R.RSRRID RECID,
    R.RSRTST STAMP,
    sum(aggrcol)/(1024*1024) MBYTES,
    sum(inpcol) INPBYTES,
    sum(outcol) OUTBYTES
  from
    x$kccrsr R,
    (
    select
      rman_status_recid,
      rman_status_stamp,
      sum(case when type=3 then blocks*block_size else 0 end) aggrcol,
      sum(case when type=1 then blocks*block_size else 0 end) inpcol,
      sum(case when type=2 then blocks*block_size else 0 end) outcol
    from
      x$ksfqp
    group by
      rman_status_recid,
      rman_status_stamp) RS
  where
    R.RSRRID = RS.RMAN_STATUS_RECID(+) and R.RSRTST = RS.RMAN_STATUS_STAMP(+)
  group by
    R.RSRRID,
    R.RSRTST) HH, (
  SELECT
    unique R.RSRRID RECID,
    R.RSRTST STAMP,
    device_type
  from
    x$kccrsr R,
    (
    SELECT
      RMAN_STATUS_RECID,
      RMAN_STATUS_STAMP,
      decode(count(distinct devtype) over (partition by RMAN_STATUS_RECID, RMAN_STATUS_STAMP),1, first_value(devtype) over (partition by RMAN_STATUS_RECID, RMAN_STATUS_STAMP), 0, null,'*') device_type
    from
      x$ksfqp
    where
      2 = TYPE) RS
  where
    R.RSRRID = RS.RMAN_STATUS_RECID(+) and R.RSRTST = RS.RMAN_STATUS_STAMP(+)) ODEV
WHERE
  nvl(R1.RECID,R2.RSRRID) = HH.RECID AND nvl(R1.STAMP,R2.RSRTST) = HH.STAMP AND nvl(R1.RECID,R2.RSRRID) = ODEV.RECID AND nvl(R1.STAMP,R2.RSRTST) = ODEV.STAMP AND R2.RSRRID = R1.RECID(+) AND R2.RSRTST = R1.STAMP(+)

home |  up