Re: unable to set time zone

Hmm... It appears that Oracle guards us dumb admins against changes to the database that can lead to wrong results and disallows altering the database time zone if there's a single table with a column of TIMESTAMP WITH LOCAL TIME ZONE type in the database. Changing the db time zone will result in wrong interpretation of data stored in such columns - and Oracle rightfully denies our rash request. Thanks a lot for saving us pour souls from big trouble this dumb move could cause (as if we were not warned about the consequences in the docs). :)

Here's a query to find offending columns: select u.name || '.' || o.name || '.' || c.name TSWLTZ_column   from sys.obj$ o, sys.col$ c, sys.user$ u

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

ML note #230099.1 note says that as soon all offending columns are removed, you will be able to change the db time zone. No idea how to preserve existing data though - will EXP and then IMP with new time zone in place properly convert the tz of exported data to the new dbtz? Gotta try this out... Or maybe there's a simpler solution?

-- 
Vladimir Zakharychev (bob@dpsp-yes.com)                http://www.dpsp-yes.com
Dynamic PSP(tm) - the first true RAD toolkit for Oracle-based internet applications.
All opinions are mine and do not necessarily go in line with those of my employer.


"Michael Blaser" <miblaser_at_bluewin.ch> wrote in message news:3e78c1c8$1_5_at_news.bluewin.ch...


> 

> ALTER DATABASE SET TIME_ZONE = 'Europe/London';

> 

> But that doesn't works at all, even exactly that statement is written in the

> documentation, I get the error:

> 

> ORA-02231 missing or invalid option to ALTER DATABASE

>