Simpler Navigation for Servers and Operating Systems - Please Update Your Bookmarks
Completed: a much simpler Servers and Operating Systems section of the Community. We combined many of the older boards, so you won't have to click through so many levels to get at the information you need. Check the consolidated boards here as many sub-forums are now single boards.
If you have bookmarked forums or discussion boards in Servers and Operating Systems, we suggest you check and update them as needed.
General
cancel
Showing results for 
Search instead for 
Did you mean: 

Scripts to show how long Oracle database is up

Dave Walley
Frequent Advisor

Scripts to show how long Oracle database is up

Hi.

I need to know how long my database is up. Has anybody got a script to show uptime for Oracle 7 and 8 databases.

Thanks in advance

Dave
why do i do this to myself
5 REPLIES
Yogeeraj_1
Honored Contributor

Re: Scripts to show how long Oracle database is up

hi dave,

Run the following query:

SQL> select to_char(STARTUP_TIME,'Dy dd Mon HH24:MI:SS') "DB Startup Time"
from v$instance

DB Startup Time
-------------------
Mon 16 Dec 10:59:41

SQL>

Hope this helps!
Best Regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Dave Walley
Frequent Advisor

Re: Scripts to show how long Oracle database is up

That shows me the start time what I need is a script that takes that time from the current time and reports the actual uptime.

Dave
why do i do this to myself
Yogeeraj_1
Honored Contributor

Re: Scripts to show how long Oracle database is up

hi again,

in terms of days, you should:

SQL> select sysdate - (select STARTUP_TIME from v$instance)
from dual

SYSDATE-(SELECTSTARTUP_TIMEFROMV$INSTANCE)
------------------------------------------
31.1914236

SQL>

Come on! play with it ;)


Best Regards
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: Scripts to show how long Oracle database is up

Ok. ok.
Here it is! ;)


select to_char( STARTUP_TIME, 'dd-mon-yyyy hh24:mi:ss' ),
trunc( sysdate-STARTUP_TIME ) "Dy",
trunc( mod( (sysdate-STARTUP_TIME)*24, 24 ) ) "Hr",
trunc( mod( (sysdate-STARTUP_TIME)*24*60, 60 ) ) "Mi",
trunc( mod( to_char(sysdate,'SSSSS')-to_char(STARTUP_TIME,'SSSSS'), 60 ) ) "Sec",
to_char( sysdate, 'dd-mon-yyyy hh24:mi:ss' ),
sysdate-STARTUP_TIME "Tdy",
(sysdate-STARTUP_TIME)*24 "Thr",
(sysdate-STARTUP_TIME)*24*60 "Tmi",
(sysdate-STARTUP_TIME)*24*60*60 "Tsec"
from v$instance;

Dy gives you number of days between 2 dates (partial days discarded). Tdy gives you total days including fractions (eg: you'll get 1.5 for 1 and 1/2 days). Likewise for HR and THR and so on.


E.g. when run on my Database
SQL> r
1 select to_char( STARTUP_TIME, 'dd-mon-yyyy hh24:mi:ss' ),
2 trunc( sysdate-STARTUP_TIME ) "Dy",
3 trunc( mod( (sysdate-STARTUP_TIME)*24, 24 ) ) "Hr",
4 trunc( mod( (sysdate-STARTUP_TIME)*24*60, 60 ) ) "Mi",
5 trunc( mod( to_char(sysdate,'SSSSS')-to_char(STARTUP_TIME,'SSSSS'), 60 ) )
"Sec",
6 to_char( sysdate, 'dd-mon-yyyy hh24:mi:ss' ),
7 sysdate-STARTUP_TIME "Tdy",
8 (sysdate-STARTUP_TIME)*24 "Thr",
9 (sysdate-STARTUP_TIME)*24*60 "Tmi",
10 (sysdate-STARTUP_TIME)*24*60*60 "Tsec"
11* from v$instance

TO_CHAR(STARTUP_TIME Dy Hr Mi Sec
-------------------- ---------- ---------- ---------- ----------
TO_CHAR(SYSDATE,'DD- Tdy Thr Tmi Tsec
-------------------- ---------- ---------- ---------- ----------
16-dec-2002 10:59:41 31 4 41 53
16-jan-2003 15:41:34 31.1957523 748.698056 44921.8833 2695313


SQL>

Hope you have your solution now!

Cheers
Yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Brian Crabtree
Honored Contributor

Re: Scripts to show how long Oracle database is up

One other option is to use the "last_call_et" field from the v$session for the background processes. This is in seconds, and you can modify the information in any way you want.

Brian