Title: Determining Instance Duration Time

Author:Eric Chorng-En Chang, a database administrator for Apache Corporation in Houston, Texas.

When you review the V$ dynamic performance statistics, it is important to know not only the instance startup time but also the
duration time. The following script has been developed to display the instance startup and duration time.

Sample output
-------------
     INSTANCE STATE                   VALUE
========================= ==============================
Restricted Mode           False
Shutdown Pending          False
Startup Date              21-MAR-1997 04:34:00
Current Date              25-MAR-1997 13:05:59

*** TEST Uptime ***       4 Days 8 hr. 31 min. 59 sec.

REM          instance.sql
REM
REM  This script displays the state of the current instance, the
REM  instance startup time, and the instance duration time.
REM

set feedb off
set verify off
set linesize 80
set pagesize 54
set underline =
column srtseq noprint format a1
column st new_value stime noprint
column sd new_value sdate noprint
column i_key head "INSTANCE STATE"  just center format a25
column i_value head "VALUE"  just center  format a30
 select to_char(to_date(to_date(value,'J'),'DD-MON-YYYY')) SD
 from v$instance
 where key='STARTUP TIME - JULIAN'  ;
 select lpad(floor(VALUE/3600),2,'0')||
        lpad(floor(mod(VALUE,3600)/60),2,'0')||
        lpad(mod(mod(VALUE,3600),60),2,0) st
 from v$instance
 where key='STARTUP TIME - SECONDS'
 ;
clear screen
 select distinct decode(key,'STARTUP TIME - JULIAN',
                   'Startup Date ',
                   'STARTUP TIME - SECONDS',
                   'Startup Date ',nls_initcap(key)) i_key,
        decode(key,'STARTUP TIME - JULIAN',
               to_char(to_date('&sdate','DD-MON-RR'),'DD-MON-YYYY') ||
               ' '||
               to_char(to_date('&stime','HH24MISS'),'HH24:MI:SS'),
                   'STARTUP TIME - SECONDS',
               to_char(to_date('&sdate','DD-MON-RR'),'DD-MON-YYYY') ||
               ' '||
               to_char(to_date('&stime','HH24MISS'),'HH24:MI:SS'),
                    decode(value,0,'False','True')) i_value, '1' srtseq
 from v$instance
union
select chr(10)||rpad('*** '||name||' Uptime ***',25,' ') i_key,
chr(10)||to_char(
floor(sysdate-to_date('&sdate'||'&stime','DD-MON-YYHH24MISS'))) ||
       ' Days '||
to_char(
   mod(floor((sysdate-to_date('&sdate'||'&stime','DD-MON-YYHH24MISS'))
       *24),24))||' hr. '||lpad(
to_char(
 floor(mod(((sysdate-to_date('&sdate'||'&stime','DD-MON-YYHH24MISS'))
       *24*60),
        floor((sysdate-to_date('&sdate'||'&stime','DD-MON-YYHH24MISS'))
        *24)*60))),2,'0')||' min. '||lpad(
to_char(
 ceil(mod(((sysdate-to_date('&sdate'||'&stime','DD-MON-YYHH24MISS'))
        *24*60*60),
       floor((sysdate-to_date('&sdate'||'&stime','DD-MON-YYHH24MISS'))
        *24*60)))),2,'0')|| ' sec. ' i_value, '4' srtseq
from v$database
union
 select 'Current Date   ' i_key,
        to_char(sysdate,'DD-MON-YYYY')|| ' '||
        to_char(sysdate,'HH24:MI:SS') i_value, '2' srtseq
 from dual
 order by 3
/