Re: Database Timezone Error

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

David Fitzjarrell Received on Tue Jun 27 2006 - 10:35:42 CDT