Search

Join this Blog for direct reference of any post into your inbox. To join just click on "Join this Site" under "Follower" then Login with your Email.*** DECLARATION: I Maintain this Blog for Helping Myself While at Work and Welcome any body Needing Help!!!.*** CAUTION: Using any of the script from this Blog may contain at Own Risk. These scripts May or May not have been Tested.***

Sunday, 29 December 2013

How to Change DBTIMEZONE after Database Creation

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

5 comments:

  1. Hi sir.how are you. This is Haseena.I have completed my training on oracle 10g and 12c. I want to write cerification exam.so which paper i need to write. 10g or 12c?

    ReplyDelete
  2. Thanks brother Shahid. this is well explained article.

    ReplyDelete
  3. Good One. this is well explained article.

    ReplyDelete
  4. BlueHost is ultimately one of the best web-hosting provider for any hosting services you might require.

    ReplyDelete