Each time I run Oracle SQL Developer or Oracle Data Modeler I receive this error message:
ora-01882 "timezone region not found"
Digging a this issue, I found that both Oracle SQL Developer and Oracle Data Modeler says that my timezone is Europe/Berlin
, which is not listed into the Oracle's system view V$TIMEZONE_NAMES
.
So, I need to change the timezone in Oracle SQL Developer (not the database) to match the most similar timezone found in V$TIMEZONE_NAMES
.
SQL> INSERT INTO table_tsltz VALUES(2, TIMESTAMP '2003-01-01 2:00:00'); Insert the same data as a TIMESTAMP WITH TIME ZONE literal. Oracle converts the data to a TIMESTAMP WITH LOCAL TIME ZONE value. This means the time zone that is entered ( -08:00 ) is converted to the session time zone value ( -07:00 ).
I just ran select systimestamp from dual; in SQL developer on my machine, it replied with 30-MAY-16 12.49. 28.279000000 PM -05:00 . The -05:00 shows the timezone offset (5 hours behind UTC, essentially 5 hours behind GMT with no daylight savings time adjustments made to GMT). This shows the database server time zone.
From Oracle SQL Developer's menu go to: Tools > Preferences. From the Preferences dialog, select Database > NLS from the left panel. From the list of NLS parameters, enter DD-MON-RR HH24:MI:SS into the Date Format field. Save and close the dialog, done!
Use the ALTER DATABASE SET TIME_ZONE command to change the time zone of a database. This command takes either a named region such as America/Los_Angeles or an absolute offset from UTC. This example sets the time zone to UTC: ALTER DATABASE SET TIME_ZONE = '+00:00';
If you need to change the time zone of Oracle SQL Developer (or Oracle Data Modeler), then this is how to do it:
sqldeveloper/bin/sqldeveloper.conf
.AddVMOption -Duser.timezone=GMT-4
.You will need to change the value GMT-4
to one that match one of the timezones in V$TIMEZONE_NAMES
.
And that’s it!
This worked for me
AddVMOption -Duser.timezone=GMT+5
Try:
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With