Know your Oracle database startup time in Days, Hours Minute

As a database administrator, we came across situation where we need to know the database startup time, We can fetch that using startup_time from v$instance

SQL> select to_char(startup_time,'DD-MM-YYY hh24:mm:ss') startup_time from v$instance;

STARTUP_TIME

------------------

03-09-023 22:09:04 

 

What if the application team or you want to know since how many days, how many hours, and how many minutes it was started, We have modified the query for that as well

 

SET LINES 200 PAGES 2000

COL INSTANCE_NAME FOR A15

COL “INSTANCE UP TIME” FOR A65

SELECT INSTANCE_NAME,

FLOOR(((SYSDATE-STARTUP_TIME)*24*60*60)/3600/24) || 'DAYS '

|| FLOOR ((FLOOR(((SYSDATE-STARTUP_TIME)*24*60*60)) –

FLOOR(((SYSDATE-STARTUP_TIME)*24*60*60)/3600/24)*24*60*60)/3600) || ' HOURS '

|| FLOOR(FLOOR ((FLOOR(((SYSDATE-STARTUP_TIME)*24*60*60))

– FLOOR(((SYSDATE-STARTUP_TIME)*24*60*60)/3600/24)*24*60*60) –

( FLOOR ((FLOOR(((SYSDATE-STARTUP_TIME)*24*60*60)) –

FLOOR(((SYSDATE-STARTUP_TIME)*24*60*60)/3600/24)*24*60*60)/3600))*60*60)/60) || ' MINUTES ' 

“INSTANCE UP TIME” FROM V$INSTANCE;

 

This query just plays around startup_time column of v$instance view. Below is the sample output.