My Oracle Support Banner

How to change the Time Zone in Oracle Database Hosted in OCI with an Example (Doc ID 2459830.1)

Last updated on SEPTEMBER 10, 2023

Applies to:

Oracle Database Cloud Service - Version N/A to N/A [Release 1.0]
Oracle Cloud Infrastructure - Database Service - Version N/A to N/A [Release 1.0]
Information in this document applies to any platform.

Goal

How to change the Time Zone in Oracle Database Hosted in OCI with an Example. In this example, we change the Time Zone to EST (TZ=America/New_York)

The Database hosted in OCI is either 1-Node or 2-Node RAC  DB system.
By default the Time Zone is set to UTC in OCI Environment. The time is displayed in the UTC format at OS and Database Level

The steps documented in this Note also addresses the incorrect SYSDATE when connected through listener which is explained in the Note 1390015.1

Example :

$date
Sun Oct 14 14:17:41 UTC 2018

Local DB connection :
sqlplus " / as sysdba"
SQL>  select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'DD-MON-YYYYH
-----------------------------
14-OCT-2018 14:17:54

Remote DB Connection through Listener:
SQL> connect system/xxx@<listener ip address>:1521/orcl_phx2gp.sub02271647391.dbaas.oraclevcn.com
SQL>  select to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') from dual;
TO_CHAR(SYSDATE,'DD-MON-YYYYH
-----------------------------
14-OCT-2018 14:18:00

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.