SET FEED 10 LINES 150

col sid for 99999
col username format a20

var bdia  number;
var btime number;
var bdone number;

var edia  number;
var etime number;
var done  number;
var total number;

var esttime number;


col st1 format a14 heading "Startup Time"
col st2 format a14 heading "System Date"
col st3 format a12 heading "Running Time"
col st4 format a12 heading "Running Secs"

define v1=0
define v2=0

col undoblocksdone new_value v1
col undoblockstotal new_value v2

select
  to_char( startup_time, 'dd/mm/yy hh24"h"mi' ) st1,
  to_char( sysdate, 'dd/mm/yy hh24"h"mi' ) st2,
  lpad( to_char( trunc(sysdate,'YEAR') + (sysdate-startup_time-1),
  decode( trunc( sysdate-startup_time, 0 ), 0, 'fm" 0d "hh24"h"mi', 'fm""dd"d "hh24"h"mi' ) ), 12, ' ' ) st3,
  to_char( (sysdate-startup_time)*24*60*60, '999g999g990' ) st4
from v$instance;

PROMPT
PROMPT ###### DADOS DE UNDO   ##########
with blocos as
(
  SELECT SUM(UNDOBLKS) TOTAL, SUM(ACTIVEBLKS) ATIVOS, SUM(UNEXPIREDBLKS) UNEXPIRED, SUM(EXPIREDBLKS) EXPIRED
  FROM V$UNDOSTAT
),
prop as
(
  SELECT MAX(BLOCK_SIZE) BLOCK_SIZE, 1048576 MEGA FROM DBA_TABLESPACES WHERE CONTENTS = 'UNDO'
)
SELECT
  /* trunc(b.total * p.block_size / p.mega ) "Total"
 , */trunc(b.ativos * p.block_size / p.mega ) "Ativo"
 ,trunc(b.unexpired * p.block_size / p.mega ) "Não Expirado"
 ,trunc(b.expired * p.block_size / p.mega ) "Expirado"
FROM BLOCOS B
CROSS JOIN PROP P
/

PROMPT
PROMPT ########## ATIVAS      ##########
SELECT
 (SELECT SID FROM V$SESSION WHERE SADDR = SES_ADDR) SID,
 (SELECT USERNAME FROM V$SESSION WHERE SADDR = SES_ADDR) USERNAME,
 XIDUSN USN, XIDSLOT SLOT, XIDSQN SEQUENCE, STATUS, START_TIME, USED_UBLK, USED_UREC, LOG_IO, PHY_IO, TRUNC( USED_UBLK * 8 / 1024 ) UNDO_MB
FROM V$TRANSACTION
/
PROMPT ########## RECUPERANDO ##########
SELECT PID, USN, SLT SLOT, SEQ SEQUENCE, STATE STATUS, UNDOBLOCKSDONE, UNDOBLOCKSTOTAL, ROUND(UNDOBLOCKSDONE*100/UNDOBLOCKSTOTAL,2 ) SOFAR
FROM V$FAST_START_TRANSACTIONS
/

set feed off serverout on verify off
declare
  dtime number;
  ddone number;
begin
  if :bdia is null then
    :bdia  := to_char( sysdate, 'j' );
    :btime := to_char( sysdate, 'sssss' );
    :bdone := &v1.;
  else
    :edia  := to_char( sysdate, 'j' );
    :etime := to_char( sysdate, 'sssss' );
    :done  := &v1.;
    :total := &v2.;
    dtime := (:edia*86400+:etime) - (:bdia*86400+:btime);
    ddone := :done - :done;
    :esttime := dtime * :total / ddone;
  end if;
end;
/

undefine v1 v2
set feed 6 serverout off verify off