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

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
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google