Blog 
Be careful when setting the DBTIMEZONE!

Why you should not set the DBTIMEZONE to a “Location Time Zone” format in Oracle.

First of all, the DBTIMEZONE does not control at what time scheduler jobs exactly starts. When I have been taking care of my scheduler jobs recently I was wondering how do I now configure these, I came across a very simple question. After what time zone will “by hour 23” executed which I have planned here? In addition to the well known SYSDATE, I came across the DBTIMZONE parameter. What would make more sense that the database time zone controls when my >database!< scheduler jobs runs? Then it is obvious that I have to take care about summertime or daylight saving time (DST) in my case ‘Europe/Berlin’. Seems to be right !?! … I thought … NOT – I was wrong!

The first thing was to determine the actual database time zone setting:

SQL> select dbtimezone from dual;

DBTIME
------
+02:00

Next step: Simply switching the database time zone. Please, don’t do what I was trying to do (‘Europe/Berlin’)!

SQL> ALTER DATABASE SET TIME_ZONE = 'Europe/Berlin';

ALTER DATABASE SET TIME_ZONE = 'Europe/Berlin'
*
ERROR at line 1:
ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns

Thanks to the error message, I came across the true purpose of the DBTIMEZONE. „The only function of the database time zone is that it functions as a time zone in which the values of the “TIMESTAMP WITH LOCAL TIME ZONE” (TSLTZ) datatype are normalized to the current database timezone when they are stored in the database. However, these stored values are always converted into the session time zone on insert and retrieval, so the actual setting of the database time zone is more or less immaterial.“ Thus it is at first not as important as it sounds.

However, you should not set the DBTIMEZONE to a time zone that is affected by daylight saving time (DST). Thus an „OFFSET“ +00:00 ( -07:00, +02:00) or a static time zone like UTC or GMT that is not affected by summertime is recommended. The best setting would simply be +00:00. If you have an OFFSET +02:00 or similar, which in doubt was set through the CREATE DATABASE, you can leave it. In my case above (+02:00) everything is fine.

DATABASE SET TIME_ZONE = '+00:00';
DATABASE SET TIME_ZONE = 'UTC';

For the actual implementation a restart of the database is required. Do you want to change the DBTIMEZONE anyway or really have a “false“ value, a location time zone with DST and get the same error message like me, you first have to locate the tables that uses the data type LOCAL TIME ZONE WITH LOCAL TIMESTAMP:

ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns
select t.owner, t.table_name, t.column_name, t.data_type
  from dba_tab_cols t, dba_objects o
 where t.data_type like '%WITH LOCAL TIME ZONE'
   and t.owner=o.owner
   and t.table_name = o.object_name
   and o.object_type = 'TABLE'
order by 1
/

In the second step you should export the appropriate tables and drop them. Set DBTIMEZONE, bounce the database, restart and import the tables. If you don’t want to drop the tables, because it’s only a sample schema you could alternatively set the relevant column to DATE and afterwards back to TIMESTAMP(6) WITH LOCAL TIME ZONE. But caution, information will be lost with the last alternative.

And what about my scheduler jobs now? They are controled by the time set on my server. If you have problems that your database jobs do not start when they are supposed to, you should verify your server settings and OS time:

$ date

Sun Apr 24 14:36:01 CEST 2016

Check OS time from the database by using SYSTIMESTAMP:

SQL> select SYSTIMESTAMP from dual;

SYSTIMESTAMP
-----------------------------------------------------------
24-APR-16 02.36.20.699547 PM +02:00

Finally, an interesting document that deals with topic date and time in the Oracle database at MOS: Doc ID 340512.1

5 comments on “Be careful when setting the DBTIMEZONE!

Thanks Sebastian for taking the time to explain this. I really helped me understand.

Not sure, why you are setting dbtimezone to change the scheduler timezone. As usually in oracle, they are regulated by two different parameters (Note 467722.1).

To set up the proper default time zone in scheduler, the following should be used:
select value from dba_scheduler_global_attribute WHERE attribute_name = ‘DEFAULT_TIMEZONE’ ;
exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE(‘default_timezone’,’Europe/Berlin’);

Nevertheless, the better way is to set the [b]proper session zone [/b]and then create the job (it’s one of the cases from the note 340512.1):
ALTER SESSION SET TIME_ZONE = ‘Europe/Berlin’;
exec dbms_scheduler.create_job( bla bla bla) ;

Few notes just for the reference:
DBMS_SCHEDULER or DBMS_JOB And DST / Timezones Explained (Doc ID 467722.1)
Impact of Changes to Daylight Saving Time (DST) Rules on the Oracle Database (Doc ID 357056.1)
How to Determine Whether Time Zone Changes Will Affect Your Database (Doc ID 406410.1)
Timestamps & time zones – Frequently Asked Questions (Doc ID 340512.1)

And, by the way, why exactly should not we set the DBTIMEZONE to a “Location Time Zone” format? 😀

even if it’s boring … did you read the article – first and last sentence let me doubt?

But thank you for a long list of Doc IDs.

You mentioned Doc ID 340512.1 – read carefully:

“The dbtimezone should be set to an offset (+00:00 , -05:00 or +09:00 for example) or a static time zone that is not affected by DST (like UTC or GMT ) , NOT to a named timezone that is affected by DST (Like Europe/Brussels or US/Central) .”

thanks Nice

Relying on the server to determine absolute time via sysdate seems problematic to me, particularly when considering pluggable databases. For example, if I unplug a PDB and plug it in on a different server having a different timezone – and then use “sysdate” on the new server, wouldn’t the resultant date value created be at odds with the existing date values in the PDB?

Leave a Reply

Your email address will not be published. Required fields are marked *

What can CarajanDB do for you?