- 14 Aug, 2020
- 0 Comments
- 53 Secs Read
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