Home » RDBMS Server » Server Administration » problem when setting new DBTIMEZONE
problem when setting new DBTIMEZONE [message #213317] Wed, 10 January 2007 05:09 Go to next message
aline
Messages: 92
Registered: February 2002
Member
hello,

I have a little problem when changing a dbtimezone.

11:59:06 SQL>  connect / as sysdba
Connected.
11:59:42 SQL> alter database set time_zone='Europe/London';
alter database set time_zone='Europe/London'
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE



any idea?
thk Smile
Re: problem when setting new DBTIMEZONE [message #213320 is a reply to message #213317] Wed, 10 January 2007 05:27 Go to previous messageGo to next message
Littlefoot
Messages: 20901
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Does your database version support this ALTER DATABASE option?

Check list of available time zones by querying v$timezone_names; perhaps you specified an invalid one?

SELECT DISTINCT tzname FROM v$timezone_names;
Re: problem when setting new DBTIMEZONE [message #213322 is a reply to message #213320] Wed, 10 January 2007 05:32 Go to previous messageGo to next message
aline
Messages: 92
Registered: February 2002
Member
hi thk for the answer
12:30:24 SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.7.0 - 64bit Production
PL/SQL Release 9.2.0.7.0 - Production
CORE    9.2.0.7.0       Production
TNS for Linux: Version 9.2.0.7.0 - Production
NLSRTL Version 9.2.0.7.0 - Production

12:30:33 SQL> SELECT DISTINCT tzname FROM v$timezone_names where tzname='Europe/London';

TZNAME
----------------------------------------------------------------
Europe/London

12:31:03 SQL> SELECT DBTIMEZONE FROM DUAL;

DBTIME
------
+02:00



So, my database version support this alter DATABASE option and the line his here in the v$timezone_name!
Re: problem when setting new DBTIMEZONE [message #213324 is a reply to message #213317] Wed, 10 January 2007 05:43 Go to previous messageGo to next message
ebrian
Messages: 2794
Registered: April 2006
Senior Member
Do you have any tables with TIMESTAMP WITH LOCAL TIME ZONE datatypes? If so, then you can not change the time_zone.
Re: problem when setting new DBTIMEZONE [message #213361 is a reply to message #213317] Wed, 10 January 2007 07:39 Go to previous message
aline
Messages: 92
Registered: February 2002
Member
excellent! U are right.

thk for the answer
Previous Topic: Gathering Satistics - External
Next Topic: How can I install Server on SCO UNIX 2.1 with Oracle7?
Goto Forum:
  


Current Time: Fri Dec 09 05:48:12 CST 2016

Total time taken to generate the page: 0.10839 seconds