Managing timezones in an Oracle Real Application Clusters (RAC) environment can be crucial for ensuring that your applications and services run smoothly across different regions. In this blog post, we’ll walk you through the steps to change the timezone for both user-level and service-level using triggers.

Create a Trigger for User-Level Timezone Change

Change the time zone for particular user with help of trigger. Suppose SCOTT is my user when ever any session is created with SCOTT its time zone set to ‘America/New York’

CREATE OR REPLACE TRIGGER set_user_timezone
AFTER LOGON ON DATABASE WHEN (USER='SCOTT')
BEGIN
  DECLARE
    v_username VARCHAR2(30);
  BEGIN
    SELECT USER INTO v_username FROM DUAL;
    EXECUTE IMMEDIATE 'ALTER SESSION SET TIME_ZONE = ''America/New_York''';
    DBMS_OUTPUT.PUT_LINE('User ' || v_username || ' has logged in and timezone is set to America/New_York');
  END;
END;
/

Create a Trigger for Service-Level Timezone Change

we’ll create a trigger to set the timezone for a specific service. Suppose my service name is “my_service” and set its timezone when every any connection is make from this service as “Europe/London”. This is useful if you have different services running in different timezones.

CREATE OR REPLACE TRIGGER set_service_timezone
AFTER STARTUP ON DATABASE
BEGIN
  IF SYS_CONTEXT('USERENV', 'SERVICE_NAME') = 'my_service' THEN
    EXECUTE IMMEDIATE 'ALTER SESSION SET TIME_ZONE = ''Europe/London''';
  END IF;
END;
/