HELP! how to find out when last time db started

Help, How do i find out when the last time the database started??? Thanks!!

on Oracle 8i:

select to_char(startup_time, 'DD-MON-YY HH24:mi:ss')
from v$instance
/

Or look in the alert.log

Terry

off the top of my head i'd say you could look back in the alert log if you have it saved from that long ago.

heh was browsing views for names which sounded like they would have the info and didn't see TerryD post. i must have missed v$instance :)

Just trying to work it out for 7.3.4 but I've got to run off and do something else.....

anyone know how to get the startup time from these two values:

v$instance:

Key: STARTUP TIME - JULIAN
Value: 2451865

Key: STARTUP TIME - SECONDS
Value: 80538

The answer is about 22:20 on 16th Nov 2000.

Julian date converter: http://aa.usno.navy.mil/AA/data/docs/JulianDate.html You can view source to find the logic to convert Julian to a normal date. There might be an oracle function *shrug*

For the seconds one just divide by 60 to get minutes, divide by 60 to get hours, and subtract from when you pulled the date. In EST at least it would put startup at about Nov 16 12 noon (about 10:00 when you posted minus about 22 hours), which is what the julian date converter gets.

However that's not 22:20 that you mentioned. Did you pull that time from the alert log?

According to metalink, you need to run this for 7.3

select to_char(to_date(value, 'J'), 'DD-MM-YYYY HH24:MM:SS')
from v$instance,
where key = 'STARTUP TIME - JULIAN'
/

but this gave me the wrong value, so I tried linking the table to itself - since I guess you then need seconds past midnight:

select to_char(to_date(a.value, 'J') + to_date(b.value, 'SSSS'), 'DD-MM-YYYY HH24:MM:SS')
from v$instance a, v$instance b
where a.key = 'STARTUP TIME - JULIAN'
and b.key = 'STARTUP TIME - SECONDS'

but this said I can't add date + date (I'm not to good with date funtions!).

My alert.log shows startup at 22:22 on 16th Nov.

I haven't done my date functions homework either but I think I remember reading somewhere that you can't add dates (as you found out the hard way), that you want to add the character value to the date. Maybe if you to_char the seconds value, and add it to the to_date julian value? or vice versa?

I suppose this is why they ditched that notation and just used startup_time :)

select to_char(to_date('2451865', 'J')) from dual gives us Nov 16th.

80538/60/60 = 22.37 hours, .37 hours = 22 minutes.

Nov16th + 22 hours and 22 minutes gives Nov16th at about 22:20

As for doing it in one query, that's for someone who knows more about date functions than us :)

[Edited by pwoneill on 11-17-2000 at 11:59 AM]

We can get 22.37 hours from the 80538/60/60. What we need is to to_char the julian date to get Nov16th at 12:00am, then add 22.37 to it. I'm trying but when it adds 22 it's adding days instead of minutes. Let me play a little longer.

Edit:

Run this

select to_char(to_date('2451865', 'J') + 80538/60/60/24, 'DD-MM-YYYY HH24:MI:SS') from dual

Just change my hard coded numbers to your startup time value's and it should work.

[Edited by pwoneill on 11-17-2000 at 12:15 PM]

select sid,username,machine,to_char(LOGON_TIME,'dd-mon-yyyy hh:mi:ss pm') from v$session order by username;

this will tell for all users .where username is null when the database is started

Why breaking the head for julian and all.

this will be easy the work.

What I don't understand is I think I ran this earlier:

select to_char(to_date(a.value, 'J') + (b.value/60/60/24), 'DD-MM-YYYY HH24:MI:SS')
from v$instance a, v$instance b
where a.key = 'STARTUP TIME - JULIAN'
and b.key = 'STARTUP TIME - SECONDS'
/

and it came up with 22:10 ?!

I type it in now and it comes up with 22:22!

Guess there must be a suttle difference, but I've closed the SQL *Plus window it was in now.... Oh Well.!

Thanks

Why terry complicate when things can be made simple..

because problem solving is cool :)

plus now i know a bit more about dates.

however getting the time the oracle processes connected seems like an easier way for general use :) just want to select where username is null so you don't have to filter through normal user sessions.

Well, looking in v$instance in Oracle 8i is the easiest way of all.

I just wondered how it was done from v$Instance in Oracle 7 - and I too learnt a little more about data handling.

Why not just run:

select min(to_char(LOGON_TIME,'dd-mon-yyyy hh:mi:ss pm'))
from v$session

That's even easier still for Oracle 7... Although once you have a query written, any can be used just as quickly :)

[Edited by TerryD on 11-17-2000 at 12:37 PM]