Home » RDBMS Server » Server Administration » database time
database time [message #242807] Tue, 05 June 2007 03:41 Go to next message
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
Re: database time [message #242831 is a reply to message #242807] Tue, 05 June 2007 04:31 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
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


Re: database time [message #242837 is a reply to message #242807] Tue, 05 June 2007 04:47 Go to previous messageGo to next message
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
Re: database time [message #242850 is a reply to message #242837] Tue, 05 June 2007 05:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Post your full Oracle version.

Regards
Michel
Re: database time [message #243231 is a reply to message #242807] Wed, 06 June 2007 10:07 Go to previous messageGo to next message
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.
Re: database time [message #243247 is a reply to message #242837] Wed, 06 June 2007 10:46 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is "alter database set ..." without the ORCL.

Regards
Michel
Re: database time [message #243336 is a reply to message #243247] Wed, 06 June 2007 23:51 Go to previous messageGo to next message
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
Re: database time [message #243339 is a reply to message #243336] Thu, 07 June 2007 00:01 Go to previous messageGo to next message
DreamzZ
Messages: 1666
Registered: May 2007
Location: Dreamzland
Senior Member
look at this

http://www.dbasupport.com/oracle/ora9i/TimeZone.shtml
Re: database time [message #243342 is a reply to message #243336] Thu, 07 June 2007 00:09 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
I tried, it should work, post:
"select * from v$version;"

Regards
Michel
Re: database time [message #243348 is a reply to message #242807] Thu, 07 June 2007 00:39 Go to previous messageGo to next message
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 Dead
Re: database time [message #243359 is a reply to message #243348] Thu, 07 June 2007 01:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
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

Re: database time [message #243813 is a reply to message #242807] Sat, 09 June 2007 04:14 Go to previous messageGo to next message
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

Please... help! Confused
Re: database time [message #243817 is a reply to message #243813] Sat, 09 June 2007 04:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
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
Re: database time [message #243833 is a reply to message #242807] Sat, 09 June 2007 13:31 Go to previous messageGo to next message
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?
Re: database time [message #243835 is a reply to message #243833] Sat, 09 June 2007 13:46 Go to previous message
Michel Cadot
Messages: 64151
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
Previous Topic: reducing datafiles free space
Next Topic: Problem convertion of AL32UTF8 TO AL16UTF16
Goto Forum:
  


Current Time: Fri Dec 09 21:46:16 CST 2016

Total time taken to generate the page: 0.10043 seconds