Re: unable to set time zone

Hurrai . . . I didi it

Unbelieveable . . . there was just 1 table in the example tablespace that had a column of TIMESTAMP WITH LOCAL TIME ZONE. After I changeed the datatype I was able the set the time:zone.

Thanks a lot . . . and have a nice weekend.

thx Mike

"Vladimir M. Zakharychev" <bob_at_dpsp-yes.com> schrieb im Newsbeitrag news:b5ai2s$eqc$1_at_babylon.agtel.net...
> 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_at_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
> >
>
Received on Thu Mar 20 2003 - 16:13:18 CST