OraFAQ Forum: Server Administration

Home » RDBMS Server » Server Administration » Time Zone

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Time Zone [message #117734]

Fri, 29 April 2005 06:40

Anand Ramaswamy
Messages: 111
Registered: January 2005

Senior Member

Hi All,
When I was trying to execute the following statement, it fails. Can anyone tell me why?

SQL> ALTER DATABASE SET TIME_ZONE='+5:30';
ALTER DATABASE SET TIME_ZONE='+5:30'
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE

I checked the syntax with the documentation, everything appear to be fine.

I even tried this, which also fails.

SQL> ALTER DATABASE SET TIME_ZONE=
2 'Europe/London';
ALTER DATABASE SET TIME_ZONE=
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE

All these were tried from sysdba login.

Thanks in advance.
qA

Report message to a moderator

Re: Time Zone [message #117736 is a reply to message #117734]

Fri, 29 April 2005 06:53

Frank
Messages: 7901
Registered: March 2000

Senior Member

omit the '=':

alter database set time_zone '+5:30';

hth

Report message to a moderator

Re: Time Zone [message #117738 is a reply to message #117736]

Fri, 29 April 2005 06:58

Anand Ramaswamy
Messages: 111
Registered: January 2005

Senior Member

Doesn't work

It says :
SQL> alter database set time_zone '+5:30';
alter database set time_zone '+5:30'
*
ERROR at line 1:
ORA-00927: missing equal sign

Regards,
qA

Report message to a moderator

Re: Time Zone [message #117749 is a reply to message #117738]

Fri, 29 April 2005 07:44

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

what is the version?
works good for me in 9i logged with DBA rights

  1* alter database set time_zone='+5:30'
scott@9i > /

Database altered.

Report message to a moderator

Re: Time Zone [message #117751 is a reply to message #117734]

Fri, 29 April 2005 07:45

Frank
Messages: 7901
Registered: March 2000

Senior Member

Sorry, checked the docs and found indeed the equal sign was needed. Found my original answer in a syntax-definition on a website I frequently use; never tested it though (don't want to upset my collegues Smile

Frank

[Updated on: Fri, 29 April 2005 07:45]

Report message to a moderator

Re: Time Zone [message #117754 is a reply to message #117749]

Fri, 29 April 2005 07:51

Anand Ramaswamy
Messages: 111
Registered: January 2005

Senior Member

Hi,
Thanks for the reply. I am using Oracle 9.2.0.1

Regards,
qA

Report message to a moderator

Re: Time Zone [message #117759 is a reply to message #117754]

Fri, 29 April 2005 08:29

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

This might be a restriction ( and lasttime i read, is supposed to be fixed in 10g).
Check your database.
There should columns that are using the present local timezone.
You cannot alter the database timezone, without cleaning / dropping those columns.

Report message to a moderator

Re: Time Zone [message #117764 is a reply to message #117759]

Fri, 29 April 2005 08:47

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

scott@9i >  alter database set time_zone='+5:30';

Database altered.

scott@9i > create table mytimetable (column1 Timestamp with local time zone);

Table created.

scott@9i > insert into mytimetable values (sysdate);

1 row created.

scott@9i > commit;

Commit complete.

scott@9i > select * from mytimetable;

COLUMN1
---------------------------------------------------------------------------
29-APR-05 08.31.22.000000 AM

scott@9i > alter database set time_zone='+5:30';
alter database set time_zone='+5:30'
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE

scott@9i > desc another_mytimetable
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------------------------------
 COLUM1                                                         DATE


 

   1* insert into another_mytimetable (select * from mytimetable)
scott@9i > /

1 row created.

scott@9i > commit;

Commit complete.

scott@9i > select * from another_mytimetable;

COLUM1
---------
29-APR-05

scott@9i > drop table mytimetable;

Table dropped.

scott@9i > alter database set time_zone='+5:30';

Database altered.

Report message to a moderator

Re: Time Zone [message #117767 is a reply to message #117764]

Fri, 29 April 2005 09:10

Anand Ramaswamy
Messages: 111
Registered: January 2005

Senior Member

Thanks for explaining with an example. But can you please explain the logic behind it?

Thanks & Regards,
qA

Report message to a moderator

Re: Time Zone [message #117771 is a reply to message #117767]

Fri, 29 April 2005 09:35

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

http://www.dbasupport.com/oracle/ora9i/TimeZone.shtml

Honestly, a google search / quick look into docs might help.
Better wordings, better explained and documented.

Report message to a moderator

Previous Topic:	How to install Oracle9i from command line interface
Next Topic:	Oracle10g ASM I/O Balancing query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 26 04:42:02 CST 2025