Home » RDBMS Server » Server Administration » mls_date_format
mls_date_format [message #63800] Tue, 09 November 2004 00:14 Go to next message
AL
Messages: 24
Registered: October 2000
Junior Member
Hi,

I have oracle 9.2.0.4. on Redhat.

There are running several instances.

Now I have to crate one instance with the nls_date_format = "YYYY.MM.DD HH24:MI:SS". There are setted no values in the environment variables (and I don't want to because I don't want to change the settings of the other db's).

The standard value of the db's is DD-MON-RR. So I changed the value in the init.ora (spfile), but there are no effect!

Now I don't know why this causes no results!

Normally Session-Parameters are stronger than instance-parameters. And instance-parameters stronger than db-parameters.

But there are no session-parameters (no environment settings about nls_date), so the instance-parametrs should work!

I connect directly on the server, so no client-setting could disturb! Where is then the problem?

 
Re: mls_date_format [message #63802 is a reply to message #63800] Tue, 09 November 2004 02:23 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>>init.ora (spfile),

init.ora and spfile are two different files.
In 9i spfilesid.ora is the default.
If you did your changes in initsid.ora, start the instance with THIS initsid.ora , check whether the parameters are in effect and if so, create an spfile from THIS pfile.
shutdown the database.
startover again with default options.
Re: mls_date_format [message #63804 is a reply to message #63802] Tue, 09 November 2004 02:48 Go to previous messageGo to next message
AL
Messages: 24
Registered: October 2000
Junior Member
Äh...sorry! My fault. My description was maybe a little bit confusing.
I meant I changed pfile AND Spfile and started which each one several times and there was no effect.

Where get this parameter its value from?
Re: mls_date_format [message #63805 is a reply to message #63804] Tue, 09 November 2004 03:04 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
It works for me~.
check your login.sql or glogin.sql in $ORACLE_HOME/sqlplus/admin 
aNy explicit NLS settings in these files will reflected in your sqlplus session.
also check for any database startup triggers that may have these settings.
--
-- before changes.
--
bash-2.03$ sqlplus mag/mag

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Nov 9 07:59:52 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - 64bit Production
With the Partitioning option
JServer Release 9.2.0.4.0 - Production

mag@mutation_mutation > select sysdate from dual;

SYSDATE
---------
09-NOV-04

--
-- Now i added this entry to my init.ora
-- SHutdown and start again with tHIS init.ora
-- YOU can see the NLSdateformat is ALL set.
bash-2.03$ cat initmutation.ora | grep nls
nls_date_format='YYYY.MM.DD HH24:MI:SS'

bash-2.03$ sqlplus "sys/sys as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Tue Nov 9 08:02:34 2004

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Connected to an idle instance.

Enter value for gname: mutation
mutation > startup open pfile='initmutation.ora';
ORACLE instance started.

Total System Global Area  286755168 bytes
Fixed Size                   731488 bytes
Variable Size             167772160 bytes
Database Buffers          117440512 bytes
Redo Buffers                 811008 bytes
Database mounted.
Database opened.
mutation > select sysdate from dual;

SYSDATE
-------------------
2004.11.09 08:03:13

--
-- NOw create a an spfile from this pfile.
--

Re: mls_date_format [message #63808 is a reply to message #63805] Tue, 09 November 2004 04:07 Go to previous messageGo to next message
AL
Messages: 24
Registered: October 2000
Junior Member
OK...I take this thing now and kick it out of the window!
I made it like you described (changing nls_format_entry in pfile and started the instance directly with this pfile), but no effect!
I changed the entry in the spfile per Enterprise Manager and restarted it, no effect!
I changed (both spfile and pfile) the entries in a w2000 test-environment on an instance which have no entries about nls_date_format in the registry and restartet it several times, no effect!

When i logged in in linux as oracle-user and query my environment-variables with 'env', will I get all relevant settings?
Or could it be that an other entry like NLS_lang overwrite my pfile and spfile settings?
Re: mls_date_format [message #63810 is a reply to message #63808] Tue, 09 November 2004 04:53 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
yes.
CHECK your env.
Look for all your NLS_* parameters.
Re: mls_date_format [message #63811 is a reply to message #63810] Tue, 09 November 2004 05:11 Go to previous messageGo to next message
AL
Messages: 24
Registered: October 2000
Junior Member
There is nothing else exept nls_lang=AMERICAN_AMERICA.WE8ISO8859P1.
I made now a new test-instance with the new nls_date_format in my windows test-environment...no effect!
There must be something, which I overlook...
Re: mls_date_format [message #63817 is a reply to message #63810] Tue, 09 November 2004 22:25 Go to previous message
AL
Messages: 24
Registered: October 2000
Junior Member
Is it possible, that the nls_lang-Parameter overrides the nls_date_format from the pfile and spfile?
Is then the default value of nls_date_format at WE8ISO8859P1 and WE8MSWIN1252 DD-MM-RR?

And it is so impossible to change the nls_date_format for one specifisc instance execpt from changing the registry on the clients?
Previous Topic: Distributed option is in FALSE status.
Next Topic: MTS mode
Goto Forum:
  


Current Time: Fri Sep 27 06:23:14 CDT 2024