{"id":270,"date":"2016-04-24T08:05:00","date_gmt":"2016-04-24T06:05:00","guid":{"rendered":"https:\/\/www.carajandb.com\/2016\/04\/24\/be-careful-when-setting-the-dbtimezone\/"},"modified":"2018-03-16T10:11:22","modified_gmt":"2018-03-16T09:11:22","slug":"be-careful-when-setting-the-dbtimezone","status":"publish","type":"post","link":"https:\/\/carajandb.com\/en\/2016\/04\/24\/be-careful-when-setting-the-dbtimezone\/","title":{"rendered":"Be careful when setting the DBTIMEZONE!"},"content":{"rendered":"

Why you should not set the DBTIMEZONE to a “Location Time Zone” format in Oracle.<\/h2>\n

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 !?! \u2026 I thought \u2026 NOT \u2013 I was wrong!
\n
\nThe first thing was to determine the actual database time zone setting:<\/p>\n

\r\nSQL> select dbtimezone from dual;\r\n\r\nDBTIME\r\n------\r\n+02:00\r\n<\/pre>\n
Next step: Simply switching the database time zone. Please, don\u2019t do what I was trying to do (‘Europe\/Berlin’)!<\/p>\n
\r\nSQL> ALTER DATABASE SET TIME_ZONE = 'Europe\/Berlin';\r\n\r\nALTER DATABASE SET TIME_ZONE = 'Europe\/Berlin'\r\n*\r\nERROR at line 1:\r\nORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns\r\n<\/pre>\n
Thanks to the error message, I came across the true purpose of the DBTIMEZONE. \u201eThe 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.\u201c Thus it is at first not as important as it sounds.<\/p>\n
However, you should not set the DBTIMEZONE to a time zone that is affected by daylight saving time (DST). Thus an \u201eOFFSET\u201c +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.<\/p>\n
DATABASE SET TIME_ZONE = '+00:00';<\/pre>\n
DATABASE SET TIME_ZONE = 'UTC';<\/pre>\n
For the actual implementation a restart of the database is required. Do you want to change the DBTIMEZONE anyway or really have a \u201cfalse\u201c 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:<\/p>\n
ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns<\/pre>\n
\r\nselect t.owner, t.table_name, t.column_name, t.data_type\r\n  from dba_tab_cols t, dba_objects o\r\n where t.data_type like '%WITH LOCAL TIME ZONE'\r\n   and t.owner=o.owner\r\n   and t.table_name = o.object_name\r\n   and o.object_type = 'TABLE'\r\norder by 1\r\n\/\r\n<\/pre>\n
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\u2019t want to drop the tables, because it\u2019s 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.<\/p>\n
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:<\/p>\n
\r\n$ date\r\n\r\nSun Apr 24 14:36:01 CEST 2016\r\n<\/pre>\n
Check OS time from the database by using SYSTIMESTAMP:<\/p>\n
\r\nSQL> select SYSTIMESTAMP from dual;\r\n\r\nSYSTIMESTAMP\r\n-----------------------------------------------------------\r\n24-APR-16 02.36.20.699547 PM +02:00\r\n<\/pre>\n
Finally, an interesting document that deals with topic date and time in the Oracle database at MOS: Doc ID 340512.1<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"
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 […]<\/p>\n","protected":false},"author":3,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"_crdt_document":"","_uag_custom_page_level_css":"","site-sidebar-layout":"default","site-content-layout":"","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"var(--ast-global-color-4)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[],"tags":[223,228,224,226,227],"class_list":["post-270","post","type-post","status-publish","format-standard","hentry","tag-dbtimezone","tag-ora-30079","tag-systimestamp","tag-time_zone","tag-timestamp-with-local-time-zone"],"acf":{"occupation":"Write the Occupation of the Person","person_can-be-speaker":true,"person_is-team":true,"person_related-user-account":null,"person_ordering-number":50,"publication_speakers":null,"publication_content-language":["de"],"publication_files":null,"publication_event":"","publication_date":null},"spectra_custom_meta":{"_fgj2wp_old_id":["270"],"_yoast_wpseo_metadesc":["oracle, dbtimezone, systimestamp, date, time_zone, TIMESTAMP WITH LOCAL TIME ZONE, ORA-30079, dbtime, sysdate"],"_yoast_wpseo_metakeywords":["oracle, dbtimezone, systimestamp, date, time_zone, TIMESTAMP WITH LOCAL TIME ZONE, ORA-30079, dbtime, sysdate"],"layout_show-author-box":["1"],"_layout_show-author-box":["field_5a64ee9cc6490"],"_edit_lock":["1521191359:3"],"_edit_last":["3"],"_vc_post_settings":["a:1:{s:10:\"vc_grid_id\";a:0:{}}"],"_yoast_wpseo_content_score":["90"],"layout_header":["light"],"_layout_header":["field_5a48111185e5f"],"layout_background":[""],"_layout_background":["field_5a481193c0995"],"layout_hide_services":["0"],"_layout_hide_services":["field_5a4811c5c0996"],"layout_show-categories-tags":["1"],"_layout_show-categories-tags":["field_5a64eee4c6491"],"blogpost_related-publications":[""],"_blogpost_related-publications":["field_5a480debdac62"],"blogpost_related-files":[""],"_blogpost_related-files":["field_5a480f377db29"],"_yoast_wpseo_primary_category":[""],"_yoast_wpseo_focuskw_text_input":["DBTIMEZONE"],"_yoast_wpseo_focuskw":["DBTIMEZONE"],"_yoast_wpseo_linkdex":["16"],"rank_math_primary_category":[""],"rank_math_description":["oracle, dbtimezone, systimestamp, date, time_zone, TIMESTAMP WITH LOCAL TIME ZONE, ORA-30079, dbtime, sysdate"],"rank_math_focus_keyword":["DBTIMEZONE"],"rank_math_news_sitemap_robots":["index"],"rank_math_robots":["a:1:{i:0;s:5:\"index\";}"],"rank_math_seo_score":["66"],"rank_math_internal_links_processed":["1"],"wpil_sync_report3":["1"],"wpil_links_inbound_internal_count":["0"],"wpil_links_inbound_internal_count_data":["eJxLtDKwqq4FAAZPAf4="],"wpil_links_outbound_internal_count":["0"],"wpil_links_outbound_internal_count_data":["eJxLtDKwqq4FAAZPAf4="],"wpil_links_outbound_external_count":["1"],"wpil_links_outbound_external_count_data":["eJxlkU1vwjAMhv8KymG3tXU\/gAWhXbhMYttxxyikASLSOEtSMYT473M7mJh2i+3X9uM3kgM\/G14s3jk0nH14Y8UrttqKtXEHxqHi58hnnFkKhWnZYhBHXnHWB8voBVXB2T4lH3mex957DCnDIJXVmcIu177DmG+l0jFfoeo77dLKRG\/l6VnIbVgj+iXAtAaAsoZm1sxnD7LzC9Muq7pooMxgjIVst5lKwT7GJJNegvo86mL3hHMBRTmg1ASCMY1Uc87+wwwVKhiXdHCS8DfjMdTnx743CqacSaf2GMYx5AlBT15WkxvLLS3bVrdicxLe9jvjbrNovEUlk0E3KMk5hbTOjVRlcTXyuCcHdBAqaDrl11UAgotC9gkH2X060REH43Z3fzCkO2x7q8UfdXldMi7+Sj\/5y+Ubs6edZg=="],"wpil_sync_report2_time":["2024-08-27T11:47:27+00:00"],"astra_style_timestamp_css":["1776965820"],"_uag_css_file_name":["uag-css-270.css"]},"uagb_featured_image_src":{"full":false,"thumbnail":false,"medium":false,"medium_large":false,"large":false,"1536x1536":false,"2048x2048":false},"uagb_author_info":{"display_name":"Johannes Ahrends","author_link":"https:\/\/carajandb.com\/en\/author\/9aa6cdb2095bd409\/"},"uagb_comment_info":6,"uagb_excerpt":"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…","_links":{"self":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/posts\/270","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/users\/3"}],"replies":[{"embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/comments?post=270"}],"version-history":[{"count":1,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/posts\/270\/revisions"}],"predecessor-version":[{"id":6886,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/posts\/270\/revisions\/6886"}],"wp:attachment":[{"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/media?parent=270"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/categories?post=270"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/carajandb.com\/en\/wp-json\/wp\/v2\/tags?post=270"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}