Operating System - HP-UX
1748236 Members
3631 Online
108759 Solutions
New Discussion юеВ

Re: 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 5
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