How to change Sysdate of Oracle Database
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 33

Thread: How to change Sysdate of Oracle Database

  1. #1
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203

    How to change Sysdate of Oracle Database

    Hi Friends,
    I need your help in a small issue I'm having. How to change Sysdate of Oracle Database. When we say 'select sysdate from dual'. now this is giving me a wrong date...(some old date due to some reason). Now I want to change the same to the current date that is synchronized with my OS date.
    Please help...
    Thanks and Regards
    Sandy
    "Greatest Rewards come only with Greatest Commitments!"

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    It's the same as the date on the host computer. Change the hos computer time
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    Thanks for the reply slimdave but I'm affraid you are not correct. I could find out the answer. You can change sysdate by setting the parameter FIXED_DATE in parameter file or by command 'Alter system...
    Hope it is alright...no hard feelings...you have desire to help people out...
    Regards
    Sandy
    "Greatest Rewards come only with Greatest Commitments!"

  4. #4
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Thanks for patronizing me Sandy, but fixed_date will just set your date to a constant. Is that what you want? I think not. You going to issue "alter system set fixed_date= ..." every second? Change your host computer clock to change Oracle sysdate
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  5. #5
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    Hi Slimdave,
    I think probably I could not put the question in a right way or you could not understood the same. I want to change my sysdate...from the current date that is wrong one to today's date that is apperently OS Date also. I need to set the paremeter just for once...
    Moreover changing OS date will never affect Oracle Database once it is operational. I think at the time of installation of Oracle software only the OS system date affects the Oracle Sysdate (though I'm not sure in this part).
    Please check out for your own advantage and let me also know...
    Thanks
    Sandy
    "Greatest Rewards come only with Greatest Commitments!"

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well if you set fixed_date="20030205 22:00:00"

    when you issue select sysdate from dual you will always see that, slimdave already pointed that out

    if you then get rid of parameter you get the old supposed wrong date again

    quoted from doc:
    FIXED_DATE lets you set a constant date that SYSDATE will always return instead of the current date. This parameter is useful primarily for testing. The value can be in the format shown above or in the default Oracle date format, without a time.

    useful for testing, nothing else nothing more

    Oracle uses date from OS, it's internal clock is SCN not date

  7. #7
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    Originally posted by sandycrab
    Moreover changing OS date will never affect Oracle Database once it is operational
    Sandy
    that is totally bull****, what do we peeps do in Spain when we change summer time and winter time then, according to your statement?

  8. #8
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    Thanks pando for the great language you use, anyway...now I got it...but then what is the answer for my original question?? I want sysdate as todays date ( I mean it should change everyday)
    And do you want to say that if I change my Unix server's date, it will reflect in my Oracle database when I issue 'select sysdate from dual'?? will it show the new date that I have just changed in my Unix Server...atleast Slimdave's statement says so...Are you sure about your words??
    "Greatest Rewards come only with Greatest Commitments!"

  9. #9
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you dont get it do you.

    Oracle reads sysdate from OS date because internally it doesnt care what's OS date, it has his own internal clock.

    Now go to SQLPLUS

    select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;

    !date

    and post here

  10. #10
    Join Date
    Jul 2002
    Location
    Lincolnshire, IL
    Posts
    203
    Dear Mr. Pando...I hope you got my problem...already my Oracle sysdate and my Unix server's date is different...would you like to throw some light on this because when I issue select sysdate from dual..it does not show my unix server's date...
    Any clue on this??
    "Greatest Rewards come only with Greatest Commitments!"

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