8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
Multitenant : PDBs With Different Time Zones to the CDB in Oracle Database 12c Release 1 (12.1)
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.
Related articles.
Container Database (CDB) Level
Setting the timezone at the container database level is the same as setting it for a non-CDB instance. The CDB setting is the default for all pluggable databases.
Check the current time zone for the container database.
CONN / AS SYSDBA SELECT dbtimezone FROM DUAL; DBTIME ------ +00:00 SQL>
Reset the time zone using the ALTER DATABASE
command to specify the new TIME_ZONE
value. The database will need to be restarted for this to take effect.
CONN / AS SYSDBA ALTER DATABASE SET TIME_ZONE='Europe/London'; SHUTDOWN IMMEDIATE; STARTUP;
We can see the database time zone has been changed.
CONN / AS SYSDBA SELECT dbtimezone FROM DUAL; DBTIMEZONE ------------- Europe/London SQL>
Pluggable Database (PDB) Level
Setting the time zone in the pluggable database allows it to override the CDB setting.
Check the current time zone for the pluggable database.
CONN / AS SYSDBA ALTER SESSION SET CONTAINER = pdb1; SELECT dbtimezone FROM DUAL; DBTIME ------ -07:00 SQL>
Reset the time zone using the ALTER DATABASE
command to specify the new TIME_ZONE
value. The pluggable database will need to be restarted for this to take effect.
CONN / AS SYSDBA ALTER SESSION SET CONTAINER = pdb1; ALTER DATABASE SET TIME_ZONE='US/Eastern'; SHUTDOWN IMMEDIATE; STARTUP;
We can see the pluggable database time zone is different to the container database.
CONN / AS SYSDBA SELECT dbtimezone FROM DUAL; DBTIMEZONE ------------- Europe/London SQL> ALTER SESSION SET CONTAINER = pdb1; SELECT dbtimezone FROM DUAL; DBTIMEZONE ---------- US/Eastern SQL>
For more information see:
Hope this helps. Regards Tim...