OraFAQ Forum: Server Administration » database time

Home » RDBMS Server » Server Administration » database time

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

database time [message #242807]

Tue, 05 June 2007 03:41

hemavb
Messages: 103
Registered: May 2007
Location: Dubai , UAE

Senior Member

i have installed oracle db on a pc where the OS time was was showing time zone Central time- USA.
After some days we changed the os time zone to Abu Dhabi, Muscat. and also reset the tim and date.

BUT, the database time is not getting set. What may be the issue? and how do i fix it?

Oracle DB : 9i Standard
O/S : Windows 2003 server Standard

Rgrds,
Hema

Report message to a moderator

Re: database time [message #242831 is a reply to message #242807]

Tue, 05 June 2007 04:31

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

SELECT DBTIMEZONE FROM DUAL;

gives you the time zone of your database (most often the one at time of creation).
ALTER DATABASE SET TIME_ZONE... allows you to change the current database time zone.

Regards
Michel

Report message to a moderator

Re: database time [message #242837 is a reply to message #242807]

Tue, 05 June 2007 04:47

hemavb
Messages: 103
Registered: May 2007
Location: Dubai , UAE

Senior Member

hi michel,

i have tried that option but it gives me the following error:


SQL> alter database orcl set time_zone = '+04:30';
alter database orcl set time_zone = '+04:30'
*
ERROR at line 1:
ORA-02231: missing or invalid option to ALTER DATABASE

what to do now.. i have checked the link that you have sent, there seems to be no problem with the syntax.

i am logged in as the sys user of the database.

-Hema

Report message to a moderator

Re: database time [message #242850 is a reply to message #242837]

Tue, 05 June 2007 05:15

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Post your full Oracle version.

Regards
Michel

Report message to a moderator

Re: database time [message #243231 is a reply to message #242807]

Wed, 06 June 2007 10:07

hemavb
Messages: 103
Registered: May 2007
Location: Dubai , UAE

Senior Member

9.2.0.1.0
this is the version of the DB.

I have checked the documentation for the same and the syntax is the same.

Report message to a moderator

Re: database time [message #243247 is a reply to message #242837]

Wed, 06 June 2007 10:46

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

This is "alter database set ..." without the ORCL.

Regards
Michel

Report message to a moderator

Re: database time [message #243336 is a reply to message #243247]

Wed, 06 June 2007 23:51

hemavb
Messages: 103
Registered: May 2007
Location: Dubai , UAE

Senior Member

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

Still the same error.
the user i am logged in to is SYS as SYSDBA and i have checked there is no change in the system privs. This user has alter database priv.

Hema

Report message to a moderator

Re: database time [message #243339 is a reply to message #243336]

Thu, 07 June 2007 00:01

DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland

Senior Member

look at this

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

Report message to a moderator

Re: database time [message #243342 is a reply to message #243336]

Thu, 07 June 2007 00:09

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I tried, it should work, post:
"select * from v$version;"

Regards
Michel

Report message to a moderator

Re: database time [message #243348 is a reply to message #242807]

Thu, 07 June 2007 00:39

hemavb
Messages: 103
Registered: May 2007
Location: Dubai , UAE

Senior Member

Michel, Dreamz

Thanks for your inputs. I have gone thru the documents and done as specified in both, unfortunately it doesn't seem to work.

I have tried the same on another database that i have loaded on the same pc. No luck, it gives the same error.

Michel,
The code that you have sent gives the following output.

SQL> select * from v$version;

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

This is the description (as per error files) of the error code that i get.

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.

"SET" does not qualify as a legal options ????

I am confused. Sad

Sad

Dead

Report message to a moderator

Re: database time [message #243359 is a reply to message #243348]

Thu, 07 June 2007 01:24

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I found it, it is bug 2435266.
The message is erroneous it should be:

SQL> alter database set time_zone='+04:30';
alter database set time_zone='+04:30'
*
ERROR at line 1:
ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns

Quote:

ORA-30079: cannot alter database timezone when database has TIMESTAMP WITH LOCAL TIME ZONE columns
Cause: An attempt was made to alter database timezone with TIMESTAMP WITH LOCAL TIME ZONE column in the database.
Action: Either do not alter database timezone or first drop all the TIMESTAMP WITH LOCAL TIME ZONE columns.

Regards
Michel

Report message to a moderator

Re: database time [message #243813 is a reply to message #242807]

Sat, 09 June 2007 04:14

hemavb
Messages: 103
Registered: May 2007
Location: Dubai , UAE

Senior Member

wow! That is amazing.

OK now how do i "drop all the TIMESTAMP WITH LOCAL TIME ZONE columns".

All the documentation that i have read so far is confusing me even more. Uh Oh

Uh Oh

Please... help! Confused

Confused

Report message to a moderator

Re: database time [message #243817 is a reply to message #243813]

Sat, 09 June 2007 04:51

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

dba_tab_columns to list them.
alter table ... drop column ... to drop them.

Regards
Michel

Report message to a moderator

Re: database time [message #243833 is a reply to message #242807]

Sat, 09 June 2007 13:31

hemavb
Messages: 103
Registered: May 2007
Location: Dubai , UAE

Senior Member

and then create the new records with the time settings i need and then day alter database right?

Report message to a moderator

Re: database time [message #243835 is a reply to message #243833]

Sat, 09 June 2007 13:46

Michel Cadot
Messages: 68686
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Sorry, I don't understand your sentence.

Depending if you want to keep or not the current data in these columns.
For myself, I export the tables with these datatype, drop the tables, alter database, import the table and update the data to reflect the change of database timezone.

Regards
Michel

Report message to a moderator

Previous Topic:	reducing datafiles free space
Next Topic:	Problem convertion of AL32UTF8 TO AL16UTF16

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Sep 19 21:28:05 CDT 2024