This content has been marked as final. Show 6 replies
The commands you posted are used to change Oracle Management Server agent timezone. It will not affect the Oracle jobs you submited in Oracle database.
The date/time your Oracle Job runs is taken from OS, you need to set your OS's timezone to correct value.
You can verify your local time zone by,
SELECT SESSIONTIMEZONE FROM DUAL;
Thanks for the reply, yingkaun.
SELECT SESSIONTIMEZONE FROM DUAL; shows the correct time zone.
SELECT current_timestamp FROM DUAL; is also correct -05:00
but SELECT dbtimezone FROM DUAL; is 2 hours earlier than the actual time -07:00
By OS are you referring to the Operating System's time?
How can I set the dbtimezone to US/Central ?
DBTIMEZONE only apply when you have "TIMESTAMP WITH LOCAL TIME ZONE" (TSLTZ) datatype.
Yes, OS = Operating System
To change your OS timezone just use Control Panel since you are on Windows.
Check Metalink doc
Timestamps & time zones - Frequently Asked Questions
Doc ID: Note:340512.1
Thanks again yingkuan.
I would still like to change dbtimezone. Can you tell me how to change it?
ALTER database SET TIME_ZONE =<Timszoen>
but you should not be have any column defined as timestamp with local timezone in the database
The metalink doc has it,
SQL> ALTER DATABASE SET TIME_ZONE = '-05:00';restart database.
Again, this didn't do any good unless you plan to use TSLTZ datatype.
If there's existing TSLTZ values this will fail.
Then check metalink
Note 230099.1 ORA-2231 When Trying to Change the Database Time Zone