There are various ways to check database uptime using a SQL query. We will basically use the view v$instance and v$session to determine the database uptime. You can also view the alert log of the database to view the startup time of the database.
Using v$instance view
The startup_time field from v$instance view provides the time at which databases was started.
SQL> select instance_name, to_char(startup_time,'mm/dd/yyyy hh24:mi:ss') as startup_time from v$instance;
Sample output:
You can look at difference between current time and startup time.
select instance_name, trunc(sysdate-startup_time) as "Days uptime" from v$instance;
You can also apply various math and formatting to get more detailed time breakdowns. For example:
select instance_number,instance_name, trunc(sysdate-startup_time) as "Days", trunc( 24*((sysdate-startup_time)- trunc(sysdate-startup_time))) as "Hours", mod(trunc(1440*((sysdate-startup_time)- trunc(sysdate-startup_time))), 60) as "Minutes", mod(trunc(86400*((sysdate-startup_time)- trunc(sysdate-startup_time))), 60) as "Seconds" from gv$instance order by instance_number;
INSTANCE_NUMBER INSTANCE_NAME Days Hours Minutes Seconds
--------------- ---------------- ---------- ---------- ---------- ----------
1 orclcdb 8 3 59 4
Using v$session view
The “logon_time” from v$session also provides us with the database uptime.
SQL> SELECT database_name, TO_CHAR(logon_time, 'DD-MM-YYYY HH24:MI:SS') FROM v$session WHERE program LIKE '%PMON%';
Sample output:
OR
SQL> SELECT database_name, to_char(logon_time,'DD/MM/YYYY HH24:MI:SS') FROM v$session WHERE sid=1 ;
here sid=1 refers to PMON ( Oracle background process)
Sample Output:
