How to Change DBTIMEZONE after Database Creation

DBTIMEZONE is a function which returns the current value of Database Time Zone. It can be queried using the example below:

SELECT DBTIMEZONE FROM DUAL;

DBTIME

------

-07:00

Please note the return type of function is Time Zone Offset. The format ([+|-] TZH: TZM) contains the lead (+) and lag (-) with hour and minutes specifications.
 
Notes:

1. Database Time zones can be queried from V$TIMEZONE_NAMES dictionary view.
2. A Time zone can be converted into Time Zone offset format using TZ_OFFSET function.

Example:

SELECT TZ_OFFSET('America/Menominee') FROM DUAL;

TZ_OFFS

--------

-06:00

3. Time zone is set during database creation or using CREATE DATABASE. It can be altered using ALTER DATABASE command. Database time zone cannot be altered if a column of type TIMESTAMP WITH [LOCAL] TIMEZONE exists in the database because TIMESTAMP with LOCAL TIMEZONE columns are stored to normalize the database. Time zone can be set in Location zone format or [+|-]HH:MM format.

In the case when you have any column with TIMESTAMP LOCAL TIMEZONE (TSLTZ) then you have to follow the backup–drop that table–change the timezone then restore that table. To check run the below query and notice the output:

Select u.name || '.' || o.name || '.' || c.name "Col TSLTZ"     

from sys.obj$ o, sys.col$ c, sys.user$ u     

where c.type# = 231 and o.obj# = c.obj# and u.user# = o.owner#;

Col TSLTZ

--------------

ASSETDVP.TEST.TSTAMP

For Example follow the below steps:

1- Backup the table that contains this column (ASSETDVP.TEST.TSTAMP Table).

2- Drop the table or the column only

3- Issue again the alter database to change the DB time Zone

4- Add the dropped column and restore the data OR restore the table if it's dropped

Example:

SQL> SELECT SESSIONTIMEZONE, DBTIMEZONE FROM DUAL;

SESSIONTIMEZONE   DBTIMEZONE

+06:00            -07:00

SQL> ALTER DATABASE SET TIME_ZONE='America/Menominee';

Database altered.

SQL> ALTER DATABASE SET TIME_ZONE='-06:00';

Database altered.

SQL> Shutdown;

SQL> Startup;

SQL> SELECT SESSIONTIMEZONE, DBTIMEZONE FROM DUAL;

SESSIONTIMEZONE   DBTIMEZONE

+06:00            +06:00

Note: Once the time zone is set, database must be bounced back to reflect this changes because alter database didn’t change the init.ora parameter.

4. Difference between SYSDATE and DBTIMEZONE- SYSDATE shows the date-time details provided by the OS on the server. It has nothing to do with TIMEZONE of the database.

5. DBTIMEZONE and SESSIONTIMEZONE are different in their operational scope. DBTIMEZONE shows the database time zone, while SESSIONTIMEZONE shows it for the session. This implies that if the time zone is altered at session level, only SESSIONTIMEZONE will change and not the DBTIMEZONE.