dbuptime.sql

-- ************************************************************************************************************
-- * Filename           : dbuptime.sql
-- * Author             : Saravanakumar K
-- * Description        : This script with show db/instance uptime  
-- * Usage              : @dbuptime.sql
-- * Version            : v1
-- *************************************************************************************************************
col inst for a80 heading 'Instance Info'
SELECT
rpad('Instance ID',15) || ' : '|| inst_id || CHR(10)
    || rpad('Instance Name',15)|| ' : '|| instance_name|| CHR(10)
    || rpad('Hostname',15)|| ' : '|| host_name|| CHR(10)
    || rpad('Version',15)|| ' : '|| version|| CHR(10)
    || rpad('Started At',15)|| ' : '|| TO_CHAR(startup_time,'DD-MON-YYYY HH24:MI:SS') || CHR(10)
    || rpad('Uptime',15)|| ' : '|| floor(SYSDATE - startup_time)|| ' days(s) '
    || trunc(24 * ( (SYSDATE - startup_time) - trunc(SYSDATE - startup_time) ) ) || ' hour(s) '
    || mod(trunc(1440 * ( (SYSDATE - startup_time) - trunc(SYSDATE - startup_time) ) ),60)   || ' minute(s) '
    || mod(trunc(86400 * ( (SYSDATE - startup_time) - trunc(SYSDATE - startup_time) ) ),60)  || ' seconds' || CHR(10)
    || rpad('Status',15)|| ' : ' || status|| CHR(10)
    || rpad('Inst Role',15)|| ' : ' || INSTANCE_ROLE|| CHR(10)
    || rpad('Logins',15)|| ' : ' || LOGINS|| CHR(10) inst
FROM gv$instance
ORDER BY inst_id;
clear col