Oracle is the world’s most popular and widely used relational database. It offers various features and services for its users. More specifically it offers a feature to manage the database and session time zone, which is essential to ensure data consistency and maintain accurate records.
This guide will provide, how to:
- Check the Database Time Zone.
- Check the Session Time Zone.
- Check Both Database and Session Time Zone.
- Set the Database Time Zone.
- Set the Session Time Zone.
How to Check and Set the Database or Session Time Zone in Oracle?
To check and set/configure the database or session time zone in Oracle, you need to log in with the “System Administrative Privileges”. To do that type the following command:
In the above command, “root1234” is the password for “SYS”.
Output
The output depicts that the user has been logged in.
How to Check the Database Time Zone?
To check the database time zone, the “SELECT” statement can be used with the “DBTIMEZONE” columns of the “DUAL” table. The command is given below:
In the above command, the “DBTIMEZONE” is used to get the database time zone.
Output
The output showed the database time zone.
How to Check/Verify the Session Time Zone?
The session time zone can be checked by getting the “SESSIONTIMEZONE” from “DUAL” using a “SELECT” statement, as follows:
Output
The output displayed the session time zone.
How to Check/Verify Both Database and Session Time Zone?
The database and session time zone can be checked together by using the “DBTIMEZONE” and “SESSIONTIMEZONE” with the “SELECT” statement. The command is mentioned below:
Output
Both database and session time zone have been displayed in the output.
How to Set the Database Time Zone?
The database time zone can be set by using the “ALTER DATABASE” command with the “SET TIME_ZONE” option to set the specific time zone. The syntax is given below:
In the above syntax, specify a specific time zone (string) in place of the “timezone” parameter.
Database time zone can be set by using the offset values or by specifying the name of a specific timezone.
Example 1: Set the Database Time Zone Using Offset Values
Let’s see an example of setting the database time zone using the offset value:
In the above example, “-04:00” is the offset value of the time zone.
Output
The output snippet showed that the database time zone has been altered successfully.
Example 2: Set the Database Time Zone Using the String
Let’s see an example of setting the database time zone using the string value:
In the above example, “America/New_York” is a valid name of a time zone.
Output
The database time zone has been altered using the string value.
How to Set the Session Time Zone?
The session time zone can be set by using the “ALTER SESSION” command, which modifies the properties of the session with the “SET TIME_ZONE” option to set the specific time zone. The syntax is given below:
The session time zone can be set by using the offset values or the string value, let’s see some examples for better understanding.
Example 1: Set the Session Time Zone Using the Offset Value
The session time zone can be set by specifying the offset value of the time zone. The example is given below:
In the above example, “+04:30” is the offset value of the time zone.
Output
The output showed that the session time zone had been altered.
Example 2: Set the Session Time Zone Using the String Value
The session time zone can be set by specifying the string value of the time zone. The example is given below:
In the above example, “Asia/Kabul” represents the name of a time zone.
Output
The output depicts that the session time zone has been changed.
How to Set Time Zone for Oracle Database and Session Using JVM?
The Oracle database and session time zone can be set by executing the java application using the “OJDBC11 driver”. The example is given below:
In the above example, “-Duser.timezone” is used to modify the default time zone to “Asia/Tokyo” and the “-jar” option is used to specify the driver file.
Output
The above screenshot showed that the default database and session time zone have been changed.
Note: This setting applies to the entire JVM and may affect other applications running within the same JVM.
Conclusion
To check the database and session time zone in Oracle, the “SELECT” statement can be used with the “DBTIMEZONE” and “SESSIONTIMEZONE” functions. In Oracle, the “ALTER” command with the SET TIME_ZONE clause can be used to set the database and session time zone. This article provided a demonstration of how to check/verify and set the database and session time zone in Oracle.
