How to query uptime for Oracle Database?

September 17, 2015
The output of dbuptime.sql has four columns. Column hostname gives the machine name, Instance gives the name of the database instance, stime gives the date and time when the database was started and uptime gives total uptime duration in days, hours, minutes and seconds.

Uptime Query for an Oracle Database

— dbuptime.sql

set serveroutput on
set wrap off
set pages 300
set lines 200
col hostname for a25
col instance for a25
col stime for a38
col uptime for a53

select
‘Hostname : ‘ || host_name as hostname
,’Instance Name : ‘ || instance_name as instance
,’Started At : ‘ || to_char(startup_time,’DD-MON-YYYY HH24:MI:SS’) as stime
,’Uptime : ‘ || 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’ as uptime
from
sys.v_$instance;
Tags:

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.