HELP! how to find out when last time db started
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 14

Thread: HELP! how to find out when last time db started

Hybrid View

  1. #1
    Join Date
    Oct 2000
    Posts
    56
    Help, How do i find out when the last time the database started??? Thanks!!

  2. #2
    Join Date
    Sep 2000
    Posts
    128
    on Oracle 8i:

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

    Or look in the alert.log

    Terry


  3. #3
    Join Date
    Jun 2000
    Posts
    417
    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.

  4. #4
    Join Date
    Jun 2000
    Posts
    417
    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 :)

  5. #5
    Join Date
    Sep 2000
    Posts
    128
    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.


  6. #6
    Join Date
    Jun 2000
    Posts
    417
    Julian date converter: [url]http://aa.usno.navy.mil/AA/data/docs/JulianDate.html[/url] 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?

  7. #7
    Join Date
    Sep 2000
    Posts
    128
    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.

  8. #8
    Join Date
    Jun 2000
    Posts
    417
    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]

  9. #9
    Join Date
    Jun 2000
    Posts
    417
    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]

  10. #10
    Join Date
    Sep 2000
    Posts
    384
    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.
    Radhakrishnan.M

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width