Oracle allows you to specify the database timezone using a time zone name, listed in the V$TIMEZONE_NAMES view, or using a UTC offset (+/-HH:MI).
For performance reasons, Oracle recommends setting the database time zone to UTC (0:00), as no conversion of time zones will be required.
The multitenant architecture allows you to specify a different database time zone for each pluggable database, with the time zone of the container database being used as the default.
When we change Database Timezone , we also need to update cluster and database Scheduler :
On standby we just need to make changes on Crs .
Note
--A PDB can have a different time zone.
--You can only change the database time zone if you have no TSLTZ columns
To locate the tables that uses the data type LOCAL TIME ZONE WITH LOCAL TIMESTAMP:
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
/
Changing Time Zone at database level
We can do only at Pdb level in case of Pluggable database environment
alter database set TIME_ZONE='+00:00';
ALTER DATABASE SET TIME_ZONE='Europe/London';
alter pluggable database <pdb1> set time_zone = '00:00';
Update Clusterware with Timezone Data:
Please note to change for Listener only if we have dedicated listener for your database other wise no need
During the GI installation, Oracle saves Timezone information in $CRS_HOME/crs/install/s_crsconfig_hostname_env.txt file, that makes TZ not to change for CRS even it is changed on OS level.
Timezone can be changed for the database using srvctl:
timedatectl status|grep zone
srvctl setenv database -d rspaws -t 'TZ=America/Phoenix'
srvctl setenv listener -l LISTENER -t 'TZ=America/Phoenix'
restart database and listener
srvctl getenv database -d rspaws
srvctl getenv listener
Changing Database Scheduler TimeZone :
select dbms_scheduler.stime from dual;
exec DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE('default_timezone','EUROPE/LONDON')
select dbms_scheduler.stime from dual;
Changing Time Zone In environment Variable :
We also need check environment variable ORA_TZFILE as that also influence Db timezone .
eg : export ORA_TZFILE = America/Phoenix
Changing Timezone for sql developer and Windows Registry :
If you need to change the time zone of Oracle SQL Developer (or Oracle Data Modeler), then this is how to do it:
Go to the installation directory of Oracle SQL Developer.
Open the file located at: sqldeveloper/bin/sqldeveloper.conf.
At the end of file, add the following line: AddVMOption -Duser.timezone=GMT-4.
Restart your Oracle Sql Developer.
Verifying Timezone in Oracle Client
Oracle client uses your TimeZone environment set at OS
Use same Timezone for Database and Oracle Client . Oracle Instant Client comes with its own timezone file imbedded in it
We can check Timezone for Oracle client using ./genezi utility under $ORACLE_HOME/bin/ .
We will see Oracle errors like ORA-01805 and ORA-01804 if Timezone for Cleint doesnt matches database Timezone
If we are using Full Oracle Client , sometime copying files under $ORACLE_HOME/oracore/zoneinfo folder from Database server to Client home does fix for ORA-01804
ORA-01804 is also reported due to missing libociei.dylib into /usr/locale/lib folder.
In worse case , to fix ORA-01804 reinstall Oracle client after fixing Timezone Environment at OS level
To Verify Environment Variable for Windows Registry
Registry Key HKCU\SOFTWARE\ORACLE\KEY_{Oracle Home Name}\ORA_SDTZ
Registry Key HKLM\SOFTWARE\ORACLE\KEY_{Oracle Home Name}\ORA_SDTZ
(resp. HKLM\SOFTWARE\Wow6432Node\ORACLE\KEY_{Oracle Home Name}\ORA_SDTZ)
Environment variable ORA_SDTZ
Current locale settings of your machine (most likely).
Database time zone if none from above is found (just an assumption)
Change the time zone at session level
-- use alter session commands
ALTER SESSION SET TIME_ZONE=local;
ALTER SESSION SET TIME_ZONE=dbtimezone;
ALTER SESSION SET TIME_ZONE='Asia/Hong_Kong';
ALTER SESSION SET TIME_ZONE='+10:00';
SELECT sessiontimezone FROM DUAL;
col SYSTIMESTAMP for a50
col STIME for a50
select (select TO_CHAR(SYSDATE,'YYYYMMDD hh24:mi') from dual) sdate,SYSTIMESTAMP, (select dbms_scheduler.stime from dual) STIME, DBTIMEZONE,( SELECT TZ_OFFSET( SESSIONTIMEZONE ) FROM DUAL) SESSIONTIMEZONE,
(SELECT TZ_OFFSET( 'EUROPE/LONDON' ) FROM DUAL) "specific_time_zone" from dual;
Views :
SELECT dbtimezone FROM DUAL;
select value$ from props$ where name = 'DBTIMEZONE';
select systimestamp from dual;
SELECT tz_version FROM registry$database;
COLUMN property_name FORMAT A30
COLUMN property_value FORMAT A20
SELECT property_name, property_value
FROM database_properties
WHERE property_name LIKE 'DST_%'
ORDER BY property_name;
set lines 240
col SYSTIMESTAMP forma a40
col CURRENT_TIMESTAMP forma a40
alter session set NLS_DATE_FORMAT='dd-MON-yyyy:hh24:mi:ss';
select dbtimezone,systimestamp,sysdate,current_timestamp,localtimestamp from dual;
References :
Configure Different Timezones per PDB (Doc ID 2143879.1)
ORA-1804: Failure to Initialize Timezone Information (Doc ID 365558.1)
