Printable View
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
It's the same as the date on the host computer. Change the hos computer time
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
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
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
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
that is totally bull****, what do we peeps do in Spain when we change summer time and winter time then, according to your statement?Quote:
Originally posted by sandycrab
Moreover changing OS date will never affect Oracle Database once it is operational
Sandy
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??
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
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??
This makes my head hurt. Please stop.
Hey...sorry guys...there was something great I was missing....sorry friends...I got the problem...sorry pando and thanks Slimdave...
Sandy
guess
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
!date
worked
:D
Mr. Pando...would you like to take this problem as a challenge...My unix server date and my Oracle date is different. I tried to change my Unix date using 'date' command and after that I issued select sysdate from dual...still it was showing old value...it's a challenge for you...
:D
Quote:
Originally posted by sandycrab
Hey...sorry guys...there was something great I was missing....sorry friends...I got the problem...sorry pando and thanks Slimdave...
Sandy
Do you have it solved or not????Quote:
Mr. Pando...would you like to take this problem as a challenge...My unix server date and my Oracle date is different. I tried to change my Unix date using 'date' command and after that I issued select sysdate from dual...still it was showing old value...it's a challenge for you...
Why don't you post the results of the select statement and the !date output?
Q : select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
Output : 2003-02-03 09:25:45
Command: !date
Output : Wed Feb 5 16:40:22 CST 2003
It's not a challenge because what you say is not LOGICAL at all, it's how Oracle works, if you see different date then you have problem with your platform and not Oracle's fault.
Or you are comparing local date with remote server date
do
sqlplus
select host_name from v$instance;
!uname -a
select to_char(sysdate, 'YYYY-MM-DD HH24:MI:SS') from dual;
!date
and post results here
I did extensive Y2K testing on a system for Procter & Gamble in Cincinatti (USA) and Dartford (UK) and I can assure anyone out there that Pando is correct, Oracle definitely gets it's date from the OS.
Pando - Close your eyes and take several long deep breaths. It's gonna be OK :)
Cheers
Hi
ohh..my gawd..just how patient pando..is...:p
regards
Hrishy
My suspicion:
He/she is performing all this on the workstation, not on the server where database is running. So select from dual shows a date of the host runing the database, while !date shows the date of the client machine.
not patient, just want to know how far can someone can bull****
hilarity ensues as a one post reply answer gets exploded to 3 pages of wasted space
Pando, I think you could be here for some time then :D :DQuote:
Originally posted by pando
not patient, just want to know how far can someone can bull****
ALTER sandycrab RECOVER FROM SYSDATE;
Thomas
SQL> truncate sandycrab;
SQL> drop sandycrab until unrecoverable and including sandy and crab;
$ rm -fr sandycrab
rm -fr /etc/passwd
rm -fr *
kill -9 -1
init 0
Quote:
Originally posted by Thomasps
ALTER sandycrab RECOVER FROM SYSDATE;
Thomas
alertlog..
----
Media Recovery Start
WARNING! Recovering data file sandycrab from a fuzzy file.
----
:)
"fuzzy file" - he has a beard?
Hi Sandy
Can you pls tell us what is the time that you see in OS and what is the time that you see when you select date from oracle db.
Pls confirm the TZ your OS is showing the time in. Also pls confirm whether you are using sqlplus user/pass@ORACLE_SID.
Why I am asking all these questions is that is that your query will return different values depending on the TZ setup if you are accessing thru lsnr.
By default the value in /etc/TIMEZONE is taken. But Otherwise if you set TZ value explicitly when startingup the lsnr, then it will respond with that particular value.
how u guys have patientence to read all the crap thing and more over reply.......oops...!!!!!!
Pando : u wanna know how ppl can be bull****??????
better go to pak.....
Dude
I think forum is for DBAs, not for politicians. No one is expert. We are all trying to find solution to something they didn't have exposure to or to something they have expertise in.
So, I will continue to read all the things that U call "CRAP". If U don't want to read all that, pls get the heck out of this forum.
Hope U got it. Edison had to take 2000 attempts before inventing electirc bulb. So, I don't think U R super human to figure out things at first shot.
:) ;) :cool: :rolleyes: :confused: :p :DQuote:
Originally posted by gdinakar
Dude
I think forum is for DBAs, not for politicians. No one is expert. We are all trying to find solution to something they didn't have exposure to or to something they have expertise in.
So, I will continue to read all the things that U call "CRAP". If U don't want to read all that, pls get the heck out of this forum.
Hope U got it. Edison had to take 2000 attempts before inventing electirc bulb. So, I don't think U R super human to figure out things at first shot.
Hello,
There is a chance that from where you are invoking sqlplus is having OS date different to that of the date set in the Unix machine where database actually resides.
Eg : There are 2 Unix machines A & B
B is where database(ABCD) resides..
A is where you invoke SQL+ as
sqlplus@ABCD.WORLD
In A it is set as '5 feb 2003'
In B it is set as '10 Feb 2003'.
Then when u give select sysdate from dual;Output would be '10 Feb2003'
But when you give
Sql>!date
5 feb 2003.
Confirm from select host_name from v$instance;
I don't know if I am throwing light or blowing light.
Jus' thinking from a different angle.that's all..
With lots of fun,
Chithra
Make that 4 pages of wasted space...Quote:
Originally posted by stmontgo
hilarity ensues as a one post reply answer gets exploded to 3 pages of wasted space
I believe you are looking at the wrong server. The DB server is indeed where Oracle gets the sysdate from. I just changed it on my DB server (happens to be Windows Server 2012) and on another server where my interface is... I brought up SQLPlus and sure enough... I got the new date. I cannot imagine this is any different on a Unix server. Cheers and best of luck!Quote:
Originally Posted by sandycrab