Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I am looking to change the timezone in a quite large Oracle database. What are some of the issues I might run into when trying to change the timezone in this way?

I don't want to 'trash' the database and am thinking of ways that the database might get trashed by changing TimeZones.

Any suggestions?

share|improve this question

migrated from Apr 25 '12 at 9:12

This question came from our site for professional and enthusiast programmers.

What do you mean - go to daylight time, or move from something like GMT to something remote like US Mountain. Dates in Oracle are stored as seconds since Jan 1 4712 BCE, and are subsequently translated into whatever TZ you want. Usually oracle uses the servers' TZ. – jim mcnamara Apr 25 '12 at 2:50
Are you on Unix or Windows? – Phil Apr 25 '12 at 9:20
What problems do you expect? – miracle173 Jul 24 '12 at 15:38

Changing the time zone is described in Oracle Database Globalization Support Guide 11g Release 2 (11.2)

share|improve this answer
the document for Oracle 12c is here – Jeff Shaw Jul 5 at 18:39

Assuming the programmers who wrote the code did so in a way that conforms to oracle recommendations, then your NLS settings are your primary concern.

If there is code that is run by job control systems like AppWorx or queueing, when you change the TZ you may fire off a large number of processes. Since a lot of that kind of processing has expectations about who runs first, code will break and you can get data corruption as a result. Also, if your job control stuff is in a separate database, then you have to consider how to deal with the TZ there as well.

Bottom line - a TZ change will affect lots of external processes, so get all of your folks together and work out a check list. Do not assume that all of your possible problems are internal to the DB.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.