1 2 Previous Next 25 Replies Latest reply: Aug 18, 2011 1:54 AM by orawiss RSS

    how to check db time zone

    sid
      Hello
      Can anyone tell me the command to check database time zone and how to update it?

      i tried SQL command
      Alter database Set Time_Zone='Asia/Karachi'

      But it gave the error
      ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH
      LOCAL TIME ZONE columns

      thanks and regards
        • 1. Re: how to check db time zone
          Fran
          Try this:
          ALTER SESSION SET TIME_ZONE='Asia/Karachi';
          • 2. Re: how to check db time zone
            Helios-GunesEROL
            Hi;

            Please see:

            ORA-2231 or ORA-30079 When Trying to Change the Database Time Zone [ID 230099.1]

            Regard
            Helios
            • 3. Re: how to check db time zone
              orawiss
              Please Read MOS ID: ORA-2231 or ORA-30079 When Trying to Change the Database Time Zone (Doc ID 230099.1)
              • 4. Re: how to check db time zone
                sid
                When i checked the database timezone with SQL

                select DBTIMEZONE from dual;
                IT gave me output
                DBTIME
                ------
                +00:00

                Now when i tried to change dbtime_zone to ASIA/KARACHI by sql
                ALTER SESSION SET TIME_ZONE='Asia/Karachi';

                It dint change the dbtimezone. Its still showing me
                +00:00
                • 5. Re: how to check db time zone
                  Fran
                  Please check this post, it helps you:
                  How to Change Time Zone in 10g
                  • 6. Re: how to check db time zone
                    orawiss
                    If you have any column with TIME STAMP WITH LOCAL TIME ZONE (TSLTZ) datatype, the "ALTER DATABASE SET TIME_ZONE =" will not work for you.

                    Please post the output of the following query:
                    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#;
                    • 7. Re: how to check db time zone
                      orawiss
                      If you have any column with TIME STAMP WITH LOCAL TIME ZONE (TSLTZ) datatype, the "ALTER DATABASE SET TIME_ZONE =" will not work for you.

                      Please post the output of the following query:
                      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#;
                      • 8. Re: how to check db time zone
                        orawiss
                        If you have any column with TIME STAMP WITH LOCAL TIME ZONE (TSLTZ) datatype, the "ALTER DATABASE SET TIME_ZONE =" will not work for you.

                        Please post the output of the following query:
                        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#;
                        • 9. Re: how to check db time zone
                          sid
                          The output is

                          2 3 4 5
                          Col TSLTZ
                          --------------------------------------------------------------------------------
                          OE.ORDERS.ORDER_DATE
                          • 10. Re: how to check db time zone
                            orawiss
                            sid wrote:
                            The output is

                            2 3 4 5
                            Col TSLTZ
                            --------------------------------------------------------------------------------
                            OE.ORDERS.ORDER_DATE
                            Ok, your option is to:

                            1- backup the table that conatins this column (OE.ORDERS 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

                            And let me know how it works!
                            • 11. Re: how to check db time zone
                              sid
                              i did these
                              1- backup the table that conatins this column (OE.ORDERS 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

                              Now when i checked the database time zone; its not showing me any update or the new time zone which i set by sql
                              select session set time_zone='Asia/Karachi'
                              • 12. Re: how to check db time zone
                                orawiss
                                sid wrote:
                                i did these
                                1- backup the table that conatins this column (OE.ORDERS 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

                                Now when i checked the database time zone; its not showing me any update or the new time zone which i set by sql
                                select session set time_zone='Asia/Karachi'
                                You have to restart your database, you did it?
                                • 13. Re: how to check db time zone
                                  sid
                                  Yes i just restarted again but timezone is still
                                  DBTIME
                                  ------
                                  +00:00

                                  I want it +05:00 !
                                  • 14. Re: how to check db time zone
                                    orawiss
                                    sid wrote:
                                    Yes i just restarted again but timezone is still
                                    DBTIME
                                    ------
                                    +00:00

                                    I want it +05:00 !
                                    This is my Test:

                                    1- backup the table that conatins this column (OE.ORDERS Table).
                                    2- Drop the table or the column only
                                    3- Issue again the alter database to change the DB time Zone
                                    4- Restart the database
                                    5- Add the dropped column and restore the data OR restore the table if it's dropped

                                    [oracle@wissem ~]$ sqlplus / as sysdba
                                    
                                    SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 17 10:42:16 2011
                                    
                                    Copyright (c) 1982, 2009, Oracle.  All rights reserved.
                                    
                                    
                                    Connected to:
                                    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
                                    With the Partitioning, OLAP, Data Mining and Real Application Testing options
                                    
                                    SQL> 
                                    SQL> 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#;
                                      2    3    4    5  
                                    no rows selected
                                    
                                    SQL> select DBTIMEZONE from dual; 
                                    
                                    DBTIME
                                    ------
                                    +00:00
                                    
                                    SQL> alter database set time_zone='Asia/Karachi';
                                    
                                    Database altered.
                                    
                                    SQL> select DBTIMEZONE from dual;
                                    
                                    DBTIME
                                    ------
                                    +00:00
                                    
                                    SQL> startup force;
                                    ORACLE instance started.
                                    
                                    Total System Global Area 1068937216 bytes
                                    Fixed Size              2220200 bytes
                                    Variable Size            725618520 bytes
                                    Database Buffers       335544320 bytes
                                    Redo Buffers              5554176 bytes
                                    Database mounted.
                                    Database opened.
                                    SQL> select DBTIMEZONE from dual;
                                    
                                    DBTIMEZONE
                                    ------------
                                    Asia/Karachi
                                    
                                    SQL> 
                                    Edited by: orawiss on Aug 17, 2011 12:03 PM
                                    1 2 Previous Next