8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

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...

Back to the Top.