Oracle-L: RE: Database up longer that host?

From: <Jared.Still_at_radisys.com>
Date: Tue, 03 Dec 2002 15:02:04 -0800
Message-ID: <F001.0051184B.20021203150204@fatcity.com>

Try:

col uptime format a40 head 'UPTIME'

select to_char(sysdate,'hh:miam')

        || ' up ' || trunc( (sysdate - startup_time) ,0) || ' days, '
        || trunc( (sysdate - trunc(sysdate)) *24 ,0)  || ':' -- hours
        || trunc( (sysdate - trunc(sysdate,'hh')) *24*60 ,0 ) || ', ' -- 
minutes
        || s.user_count || ' users' uptime
from v$instance i, (
        select count(*) user_count
        from v$session
        where username is not null

) s
/

"Stephen Andert" <stephenandert_at_firsthealth.com> Sent by: root_at_fatcity.com
12/02/2002 07:58 PM
Please respond to ORACLE-L

        To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
        cc: 
        Subject:        RE: Database up longer that host?

Govind,

Actually, what I want it the same format as I have, I just want the numbers to match (or fall within) the numbers reported by the unix uptime command for example "up 4 days, 21:08 hours". In my case, the unix uptime is saying the host was last restarted after the database startup_time reported in v$instance.

Stephen

>>> Govind.Arumugam_at_alltel.com 12/02/02 07:43PM >>>
You may use following query will give you the uptime in hours and in minutes.

select sysdate, startup_time,
round( (sysdate - startup_time) *24*60 ,0 ) uptime_in_minutes, round( (sysdate - startup_time) *24 ,0) uptime_in_hours from v$instance

SYSDATE STARTUP_TIME UPTIME_IN_MINUTES UPTIME_IN_HOURS

-------------- -------------- ----------------- ---------------
20021202203918 20021202044608               953              16

Hope this is what you wanted.

-----Original Message-----
Sent: Monday, December 02, 2002 8:34 PM
To: Multiple recipients of list ORACLE-L

On Mon, 2 Dec 2002, Stephane Faroult wrote:

> Stephen Andert wrote:
> >
> > I use a script named db_uptime.sql (I think I got it from the list

here)
> > to calculate how long the database has been up. The output compares

> > nicely to the unix uptime command.
>
>    I hope that the query doesn't come from the list, because it is
> wrong. The error is to apply floor() before multiplying by 24 or 60

-
> you have tremendous rounding errors.
> My own database has not been up long enough to be 100% sure about it but
> I believe the following to be correct :

Hi:

Neither of the scripts works for me. Try this?

select

    'Host Name : '||host_name|| chr(10)||
    'Instance Name : '||instance_name|| chr(10)||
    'Uptime : '     ||floor(xx)||'days '
                    || floor( 24 * (xx - floor(xx)) ) || 'hours '
                    || round( 60 * (24 * xx - floor(24 * xx))) ||

'minutes '

from (

             select host_name,instance_name ,(sysdate-STARTUP_TIME) xx
             from v$instance
         )

Meg Crocker

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: Meg Crocker
  INET: crocker_at_monstertrak.com 

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com 
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com 
-- 
Author: <Govind.Arumugam_at_alltel.com 
  INET: Govind.Arumugam_at_alltel.com 

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com 
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).



-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephen Andert
  INET: stephenandert_at_firsthealth.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).




-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: 
  INET: Jared.Still_at_radisys.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).

Received on Tue Dec 03 2002 - 17:02:04 CST