topic Re: Scripts to show how long Oracle database is up in Operating System - HP-UX

Scripts to show how long Oracle database is up

Dave Walley — Thu, 16 Jan 2003 11:07:55 GMT

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

Re: Scripts to show how long Oracle database is up

Yogeeraj_1 — Thu, 16 Jan 2003 11:20:06 GMT

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

Re: Scripts to show how long Oracle database is up

Dave Walley — Thu, 16 Jan 2003 11:25:16 GMT

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

Re: Scripts to show how long Oracle database is up

Yogeeraj_1 — Thu, 16 Jan 2003 11:32:18 GMT

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

Re: Scripts to show how long Oracle database is up

Yogeeraj_1 — Thu, 16 Jan 2003 11:39:46 GMT

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

Re: Scripts to show how long Oracle database is up

Brian Crabtree — Thu, 16 Jan 2003 19:27:44 GMT

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