ORA-02096 When trying to change NLS parameters

[BattleDroid42]

I'm trying to permanently change the NLS/Globalization Parameters for a new/empty database using Enterprise Manager (tried ALTER DATABASE command too):

NLS_DATE_FORMAT = 'MM/DD/YY'
NLS_TIME_FORMAT = 'HH12:MI:SS'
NLS_TIMESTAMP_FORMAT = 'MM/DD/YY HH12:MI:SS'

I DO NOT want to change for just the session or just for the instance. I want to mkae the change PERMANENTLY.

All attempts to change the parameter result in:

ORA-02096: specified initialization parameter is not modifiable with this option.

Changing the parameters in the pfile also doesn't work because the DB is running from the spfile (which is okay).

Is it neccessary to recreate the database (no big deal since it's a new database, but want to learn why this can't be changed and/or I can't find docs on this problem)? or is there a way to recreate/alter the spfile with these parameters enabled and then bounce the database?

Thanks!

 

[stefanhei]

Hi.
What scope did you specify? I guess NLS-parameters can't be modified with scope memory.
Use ALTER SYSTEM SET NLS_DATE_FORMAT = 'MM/DD/YY'
SCOPE SPFILE ... then bounce your DB.

Stefan

 

[sem]

Did you change these parameters in the correct pfile? Did you recreate spfile after changing pfile? Regards, Dima

 

[stefanhei]

In 9i you can alter with scope spfile and then create a pfile from spfile (more or less as as documentation). That way you can reduce confusion with the pfiles.

Stefan

 

[BattleDroid42]

Thanks for your responses all. Here's what I've managed to figure out so far...

This parameter is not modifiable by system and this is just an unhelpful routine error message. I've also learned that by looking at the values in V$PARAMETER you can remove all doubt and determine which params are modifiable by session (ISSES_MODIFIABLE) and which are modifiable for the system (ISSYS_MODIFABLE). Bottom line, the params I'm trying to change cannot be changed with ALTER SYSTEM with the instance running. I'm cool with that.

So the next thing I studied was the difference between the pfile and spfile (a new feature of 9i; I'm trained in 8i BTW). I found an excellent article on the subject from which I determined that I needed to add the params to the correct pfile and create a new spfile. Here's the article:

http://www.quest-pipelines.com/newsletter-v3/0502_G.htm

Here's what I did:

1) Created a new pfile in the default location from the existing spfile (the pfile in "\oracle\admin\[DBNAME]\pfile directory" isn't the right pfile...correct one was located @ "\oracle\ora92\database\"; there wasn't one so I had to make one...).
2) Shutdown
3) Added my params to the new pfile
4) Created and renamed new spfile from modified pfile
5) Startup with new spfile

EM now shows my params are entered in the spfile! Neat. However this didn't change a thing. Dates are still in DD-MMM-YY format.

I think we're all on the right track...still trying...

 

[sem]

What tool do you use to query data? Most of client applications use their own settings to alter session during startup. Sqlplus on windows uses registry settings, on Unix - environment variables. The session also may be altered from glogin.sql script.
So changing database settings may affect only applications when the environment is not set. Regards, Dima

 

[jjohnsn]

BattleDroid42,
I'm struggling down the same path you've already tread with the spfile session parameters issue. I've reaqched the point where the thread ends: EM shows nls date parameters changed in spfile but queries on nls_session_parameters don't show corresponding values. What did you learn from here?
Thanks,
Jay

 

[BattleDroid42]

It turned out that both EM and SQL*Plus stubbornly refused to display or allow me to query using my own preferred date format.

Oracle Support was also no help as usual. I started a TAR which I will never do again because they don't want to help and have a fondness for quoting and cutying and pasting from Oracle scriptures. So you are on your own that's for sure.

However, the process I followed above worked for my application and when I link into my Oracle database through ODBC, the dates display and query just the way I wanted them to. So although it may look like changing the parameters isn't working, your application my tell you otherwise.

 

[sem]

Jjohnsn and BattleDroid42: I'd suggest you read my last message. NLS parammeters changed at database level has no effect on client session, if that parameters are also defined on client side. Just change it on your pc and be happy. If you're on Windows (some your messages allow me to think so), you may set it in registry.

Regards, Dima

 

[BattleDroid42]

Yes, the environment is Windows NT/2000. Sorry but I find that ridiculous. What if there are 300 client machines? Are you saying that we're supposed to edit the registry on all 300 client machines because Oracle can't display or allow the use of a simple date format change? Not only that I tried editing the registry and it didn't work. No thank you. Anyway, yes, the problem is resolved to my satisfaction in my app. Oracle just refuses to display or allow the use of the actual desired format in OEM or SQL*Plus. I'm still having to use the stone age TO_DATE function or type DD-MMM-YY if I want to write a query there which, fortunately, I just don't. SQL Server just looks better and better...

Cheers,
BattleDroid42

 

[sem]

300 client machines? In general it should be done during installation!
I work with Oracle for many years and besides this I'm not AMERICAN_AMERICA.WE8ISO8859P1 guy, thus know something about lolization problems.
Though one you have is NOT a problem, just probably your poor knowledge of Oracle concepts. As for logging iTar, normally they deal with more complex problems, so, I suppose, can not even imagine that you ask them about this.

Regards, Dima

 

[BattleDroid42]

The fact that you've worked with Oracle for many years is not even relevant to the discussion at hand. I also doubt that your making assumptions about my knowledge of Oracle is really helping anyone. If we all knew everything about Oracle we would not be here. Finally, if Oracle handles more complex requests then this one should have been easy for them. If their attitude is that they will only handle high flown requests then this is just another reason to use a different product.

In any case good luck jjohnsn! This thread no longer holds my interest.

 

[sem]

Just to clarify my post about 'high flown requests': simple queries are normally answered by Oracle within forums hosted on Metalink. Complex issues (once their complexity is revised by Oracle persons serving forums) are normally resolved by logging iTar.
IMHO sysadmin SHOULD NOT teach user to switch power on.

Regards, Dima