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....