Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

history of uptime..

user8363520May 15 2012 — edited May 15 2012
Hello all,

Is there a way to do something similar as Mr.Tom did in his blog but instead using DBA_HIST_DATABASE_INSTANCE.... i know we can query V$instance, but i am looking to get the history now just the up since column....here is the blog Mr.Tom notes....

http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:28805759638277

The reason behind that is, If we get the query working we would like to run this thru grid, so it can run against multiple DB. And we didnt wanted to create any new objects in the db.

I treid doing something similar but the results seems very much so wrong....
select to_char(last_time,'dd-mon-yyyy hh24:mi') shutdown,
 to_char(start_time,'dd-mon-yyyy hh24:mi') startup,
 round((start_time-last_time)*24*60,2) mins_down,
 round((last_time-lag(start_time) over (order by r)),2) days_up,
 case when (lead(r) over (order by r) is null )
 then round((sysdate-start_time),2)
 end days_still_up
 from (
select r,
 to_date(last_time, 'Dy Mon DD HH24:MI:SS YYYY') last_time,
 to_date(start_time,'Dy Mon DD HH24:MI:SS YYYY') start_time
 from (
select r, startup_time, 
lag(startup_time,1) over (order by r) start_time,
lag(startup_time,2) over (order by r) last_time
from (
select rownum r, to_char(startup_time, 'Dy Mon DD HH24:MI:SS YYYY') startup_time From  DBA_HIST_DATABASE_INSTANCE))); 
i also tried looking online and here with regards to a solution before posting it on here....as i could not find any where it did had to create a object....i was looking to just query the db and get the history....
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 12 2012
Added on May 15 2012
1 comment
757 views