How To Change Database Time In Oracle

24. April 2016
Johannes Ahrends

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

Johannes Ahrends

Oracle ACE and specialist for topics like HA, migration, Standard Edition, backup and more