• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer navigation

The Geek Diary

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • VCS
  • Interview Questions
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
  • DevOps
    • Docker
    • Shell Scripting
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to check Oracle Database uptime

by admin

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:

check oracle database uptime

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:

logon_time oracle database

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:

check database uptime for oracle database

Filed Under: oracle

Some more articles you might also be interested in …

  1. How to Drop Undo Tablespace in Oracle Database
  2. Unable to instantiate disk “ASM_DISK” – error on running ‘oracleasm scandisks’ command
  3. How To Catalog Backups / Archivelogs / Datafile Copies / Controlfile Copies in Oracle Database
  4. Whats is PL/SQL
  5. How To Change A Dictionary Managed Tablespace To A Locally Managed Tablespace
  6. How to drop and recreate TEMP Tablespace in Oracle
  7. How do stubs work in a WebLogic Server cluster?
  8. Beginners Guide to Flash Recovery Area in Oracle Database
  9. How To Cancel A SQL Query In Oracle Database 18c
  10. How to Migrate ASM Disk Groups to another Storage Online [When ASMLIB Devices Are Involved]

You May Also Like

Primary Sidebar

Recent Posts

  • “aws s3 mv” Command Examples
  • “aws s3 mb” Command Examples
  • “aws s3 ls” Command Examples
  • “aws s3 cp” Command Examples

© 2023 · The Geek Diary

  • Archives
  • Contact Us
  • Copyright