OraFAQ Forum: Server Administration » how to set NLS_TIMESTAMP

Home » RDBMS Server » Server Administration » how to set NLS_TIMESTAMP_FORMAT systemwide

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

how to set NLS_TIMESTAMP_FORMAT systemwide [message #60929]

Mon, 15 March 2004 03:11

R. Schmitt
Messages: 3
Registered: March 2004

Junior Member

Hi,

I am quit new with Oracle and look for the information how to set the NLS_TIMESTAMP_FORMAT� to YYYY-MM-DD HH24.MI.SS.FF not only for a session but for the whole db, so that all applications have this setting.

Trying to "alter system" via commandline or enterprise manager failed with ORA-02096 specified initialization parameter is not modifiable with this option

�

Where else can I set this to make it persistent?

Your answers are highly welcome

Report message to a moderator

Re: how to set NLS_TIMESTAMP_FORMAT systemwide [message #60930 is a reply to message #60929]

Mon, 15 March 2004 03:20

Anatol Ciolac
Messages: 113
Registered: December 2003

Senior Member

You are very new with Oracle :-)))
Set this parameter in you init.ora file to the preferred value.

Report message to a moderator

Re: how to set NLS_TIMESTAMP_FORMAT systemwide [message #60931 is a reply to message #60930]

Mon, 15 March 2004 03:35

R. Schmitt
Messages: 3
Registered: March 2004

Junior Member

I set
nls_timestamp_format = 'YYYY-MM-DD HH24.MI.SS.FF'

in the init.ora, started and stopped the instance but the show parameters showed NO setting:

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_dual_currency string
nls_iso_currency string
nls_language string AMERICAN
nls_length_semantics string BYTE
nls_nchar_conv_excp string FALSE
nls_numeric_characters string
nls_sort string
nls_territory string AMERICA
nls_time_format string

Report message to a moderator

Re: how to set NLS_TIMESTAMP_FORMAT systemwide [message #60932 is a reply to message #60931]

Mon, 15 March 2004 04:29

Anatol Ciolac
Messages: 113
Registered: December 2003

Senior Member

possible you don't have some privileges. This is the output for 8.1.7
SYS@SGCPRO:ufamld21> show parameter nls_

NAME TYPE VALUE
------------------------------------ ------- ------------------------------
nls_calendar string
nls_comp string
nls_currency string
nls_date_format string YYYYMMDD
nls_date_language string
nls_dual_currency string
nls_iso_currency string
nls_language string AMERICAN
nls_numeric_characters string
nls_sort string
nls_territory string AMERICA

NAME TYPE VALUE
------------------------------------ ------- ------------------------------
nls_time_format string
nls_time_tz_format string
nls_timestamp_format string
nls_timestamp_tz_format string

Report message to a moderator

Re: how to set NLS_TIMESTAMP_FORMAT systemwide [message #60933 is a reply to message #60932]

Mon, 15 March 2004 04:36

R. Schmitt
Messages: 3
Registered: March 2004

Junior Member

the init.ora was changed as root. starting and stopping of the instance where done as sysdba.
is the syntax of the setting in the init.ora correct? I did not find any explicit example so far.
I just inserted a new line
# Uncomment the following line, if you want to use some of the new 8.1
# features. Please remember that using them may require some downgrade
# actions if you later decide to move back to 8.0.

#compatible = 8.1.0
nls_timestamp_format = YYYY-MM-DD HH24.MI.SS.FF
Tried also: 'YYYY-MM-DD HH24.MI.SS.FF'
and "YYYY-MM-DD HH24.MI.SS.FF"
and capital letters for the parameter names.

Report message to a moderator

Re: how to set NLS_TIMESTAMP_FORMAT systemwide [message #60934 is a reply to message #60933]

Mon, 15 March 2004 04:47

Anatol Ciolac
Messages: 113
Registered: December 2003

Senior Member

I first - you must to stop and to start database(but don't start and stop)
Second - if you are started database - this seem that you are enter correct value in init.ora(in other case database can't start)
Third - if you have all privileges on database(for example you are connected as internal) and you database version are 8.1.7 you will see some output as I wrote(indifferent what you are write in init.ora). In output of "show" command must be seen that parameter
Fourth - this is very simple problem for resolving them so many time.

Report message to a moderator

Previous Topic:	Book for Beginners?
Next Topic:	ETL Loading Method : Suggestions

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Wed Jan 22 20:11:21 CST 2025