Change database time in oracle database with Non Cdb and Cdb, Pdb database

Change database time in oracle database with Non Cdb and Cdb, Pdb database

Changing the time zone in Oracle databases involves altering the ORA_SDTZ (Oracle System Datetime Time Zone) parameter. Here's a guide for both non-CDB (Container Database) and 

CDB/PDB (Pluggable Database) scenarios:

Non-CDB (Stand-Alone Database):

Check Current Time Zone:

SELECT DBTIMEZONE FROM DUAL;

Change Time Zone:

ALTER DATABASE SET TIME_ZONE = 'desired_timezone';

Replace 'desired_timezone' with the timezone you want to set (e.g., 'America/New_York').

We can also use offset values like

ALTER DATABASE SET TIME_ZONE = '+5:30';

Verify:

SELECT DBTIMEZONE FROM DUAL;

CDB/PDB (Multitenant Database):

For the Entire CDB:

Check Current Time Zone:

SELECT TIMEZONE_NAME FROM V$TIMEZONE_FILE;

Change Time Zone for the Entire CDB:

ALTER DATABASE SET TIME_ZONE = 'desired_timezone';

Replace 'desired_timezone' with the timezone you want to set (e.g., 'America/New_York').

Verify for CDB:

SELECT TIMEZONE_NAME FROM V$TIMEZONE_FILE;

For Individual PDBs:

Check Current Time Zone for a PDB:

ALTER SESSION SET CONTAINER = pdb_name;

SELECT DBTIMEZONE FROM DUAL;

Replace 'pdb_name' with the name of your PDB.

Change Time Zone for a PDB:

ALTER SESSION SET CONTAINER = pdb_name;

ALTER DATABASE SET TIME_ZONE = 'desired_timezone';

Replace 'pdb_name' with the name of your PDB and 'desired_timezone' with the timezone you want to set.

Verify for a PDB:

ALTER SESSION SET CONTAINER = pdb_name;

SELECT DBTIMEZONE FROM DUAL;

Remember to take necessary precautions and perform these actions during a maintenance window. Changing the time zone can affect existing data, and it's recommended to thoroughly test the changes in a non-production environment before applying them to a production database.

If you like please follow and comment