How to Change Oracle 9i 10g 11g Database Time_Zone? Fix ORA-02231 & ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE (TSLTZ) columns

advertisements

_____________________________________________________________________________________________________________________

You can check the database time_zone by using following query

SQL> select dbtimezone from dual;

DBTIME

------

-07:00

You can modify the database time_zone by using following query

ALTER DATABASE SET TIME_ZONE = 'Europe/Amsterdam';

OR

ALTER DATABASE SET TIME_ZONE =’+05:30’;

You can not modify the database time_zone if any of you table columns has the data type with TIMESTAMP(n) WITH LOCAL TIME ZONE (TSLTZ). The above statements will throw following errors depending on the oracle versions.

In Oracle 9i the error would be

ORA-02231: missing or invalid option to ALTER DATABASE

And in Oracle 10g the error would be

ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH

LOCAL TIME ZONE columns

You have to find out the curresponding columns from the tables using following queries. You have to either remove or modify the data types of the columns and try the command for time_zone change.

select usr.name || '.' || obj.name || '.' || col.name TSLTZcolumn

from   sys.obj$ obj, sys.col$ col, sys.user$ usr

where  col.type#=231

and    obj.obj#=col.obj#

and    usr.user# = obj.owner#;

OR

select owner, table_name

from   dba_tab_columns
where  data_type like 'TIMESTAMP%WITH LOCAL TIME ZONE';

SQL> desc purchase_orders ;
Name Null? Type
------------------------------------ -------- ----------------------------
ORDER_ID NOT NULL NUMBER(12)
ORDER_DATE NOT NULL TIMESTAMP(6) WITH LOCAL TIME ZONE

_____________________________________________________________________________________________________________________

Website Stats