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.
