Re: Database Timezone Error

From: Vladimir M. Zakharychev <vladimir.zakharychev_at_gmail.com>
Date: 27 Jun 2006 10:31:49 -0700
Message-ID: <1151429509.846870.264640@j72g2000cwa.googlegroups.com>

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
>

SQL> select banner from v$version;

BANNER

---

Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production PL/SQL Release 9.2.0.7.0 - Production
CORE 9.2.0.7.0 Production
TNS for 32-bit Windows: Version 9.2.0.7.0 - Production NLSRTL Version 9.2.0.7.0 - Production

SQL> alter database set time_zone='Europe/Moscow'; alter database set time_zone='Europe/Moscow' *
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE

The reason is pretty valid for 9.2, specific ORA-30079 was added in 10g because ORA-02231 was misguiding and inappropriate in this case.

> 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

Don't think patching will make a difference in this particular case (though it's highly recommended anyway.) If the db was created with demos, they include OE schema, which has a table or two with TIMESTAMP WITH LOCAL TIME ZONE. If you can connect as SYSDBA, which I suppose you can do, the following query will show you offending tables:

select owner||'.'||table_name table_name   from dba_tab_columns
 where data_type like 'TIMESTAMP%WITH LOCAL%'

Get rid of these, and you will be able to change the database time zone. For future installations the most appropriate is to create the database with correct time zone right away (not sure if it's possible directly in DBCA, but you can always have it to save db creation scripts, edit them and use them to create the db.)

Hth,

    Vladimir M. Zakharychev
    N-Networks, makers of Dynamic PSP(tm)     http://www.dynamicpsp.com Received on Tue Jun 27 2006 - 12:31:49 CDT