Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Database Timezone Error
Vladimir M. Zakharychev wrote:
> fitzjarrell_at_cox.net wrote:
> > Comments embedded.
> > Khurram wrote:
> > > hi
> > >
> > > I know this question has been asked before many times but didnt find
> > > any solution.
> > >
> > > I am using Oracle 9.2.0.1.0.When i try to alter time_zone i am getting
> > > the following error
> > >
> > > SQL> ALTER DATABASE SET TIME_ZONE='-06:00';
> > > ALTER DATABASE SET TIME_ZONE='-06:00'
> > > *
> > > ERROR at line 1:
> > > ORA-02231: missing or invalid option to ALTER DATABASE
> > >
> >
> > That error is described in the documentation thus:
> >
> > ORA-02231 missing or invalid option to ALTER DATABASE
> >
> > Cause: An option other than ADD, DROP, RENAME, ARCHIVELOG,
> > NOARCHIVELOG, MOUNT, DISMOUNT, OPEN, or CLOSE is specified in the
> > statement.
> >
> > Action: Specify only legal options.
> >
> >
> > > I know its due to TIMESTAMP WITH LOCAL TIME ZONE column which store
> > > data normalised to the database timezone.Unless i dont drop this table
> > > which having TIMESTAMP
> > > WITH LOCAL TIME ZONE column,ORA-02231: missing or invalid option to
> > > ALTER DATABASE Error will occur.I searched the table in my schema as
> > > well system and sys but didnt find any table which has TIMESTAMP WITH
> > > LOCAL TIME ZONE column.
> > >
> >
> > Your 'reason' is not valid; if it were you'd receive the following
> > error instead:
> >
> > SQL> alter database set time_zone='+00:00';
> > alter database set time_zone='+00:00'
> > *
> > ERROR at line 1:
> > ORA-30079: cannot alter database timezone when database has TIMESTAMP
> > WITH
> > LOCAL TIME ZONE columns
> >
>
>
>
>
>> >
> > As it is you're receiving an error because the SET TIME_ZONE option
> > isn't available with 9.2.0.1. It is listed in the 9.2.0 documentation,
> > however I expect it's available in releases 9.2.0.4 and higher, which
> > means you'll need to patch your installation (this requires a service
> > contract). I'd be chatting with Oracle sales/support to obtain a valid
> > support agreement. Then you can patch your system and possibly have
> > access to functionality you currently lack.
> >
> > > Any idea to get rid off it??
> > >
> >
> > Certainly, and I said it above: Patch your installation.
> >
> > > Thanx
> > >
> > > Khurram
> >
>
>
>
>the next several months.
> Hth,
> Vladimir M. Zakharychev
> N-Networks, makers of Dynamic PSP(tm)
> http://www.dynamicpsp.com
Thank you for the update; the error message certainly is confusing. Unfortunately I don't have a 9iR2 database with which to play (they're all production servers). And, yes, we're going to upgrade to 10gR2 in
David Fitzjarrell Received on Tue Jun 27 2006 - 15:25:05 CDT