Change Timezone in Oracle Database for CDB and PDB

In this blog, We are going to see how to change DBTime zone in multitenent database. Oracle recommends setting the database time zone to UTC (0:00), as no conversion of time zones will be required.

CDB Level

SQL> SELECT DBTIMEZONE FROM dual;

DBTIME
------
+00:00

SQL> ALTER DATABASE SET TIME_ZONE='Europe/London';

Database altered.

SQL> SELECT dbtimezone FROM DUAL;

DBTIME
------ --- > No change. Bounce database to reflect change.
+00:00

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
SQL>
SQL> startup
ORACLE instance started.

Total System Global Area 1325400064 bytes
Fixed Size 8792488 bytes
Variable Size 822085208 bytes
Database Buffers 486539264 bytes
Redo Buffers 7983104 bytes
Database mounted.
Database opened.
SQL>
SQL>
SQL> SELECT dbtimezone FROM DUAL;

DBTIMEZONE
-------------
Europe/London

PDB Level


SQL> alter session set container=PDBPRIM;

Session altered.

SQL> SELECT dbtimezone FROM DUAL;

DBTIME
------
+00:00

SQL> ALTER DATABASE SET TIME_ZONE='Europe/London';

Database altered.

SQL> SELECT dbtimezone FROM DUAL;

DBTIME
------
+00:00

SQL> shutdown immediate;
Pluggable Database closed.
SQL>
SQL>
SQL> startup
Pluggable Database opened.
SQL> SELECT dbtimezone FROM DUAL;

DBTIMEZONE
-------------
Europe/London

Share and Enjoy !

0Shares
0 0 0

Recommended For You

ankushthavali

About the Author: ankushthavali

Leave a Reply

Your email address will not be published. Required fields are marked *