1

Is there an easy way to specify NLS parameters for the database during the installation?

We want to install a new database (environment: Windows). For testing purpose we need NLS settings for nls_language and nls_territory that differ from the default values (German instead of American). To avoid the process of dropping an recreating the database with changed init.ora I would like to specify those setting during a fresh installation on a VM.

I found no settings in the setup dialogue. Is there any way to achieve this?

environment: Oracle Database Enterprise Edition, available versions 11.2 or 12.1.

1

From the Oracle forums: https://community.oracle.com/thread/777001

It appears that NLS_DATABASE_PARAMETERS is essentially a permanent copy of the values in NLS_INSTANCE_PARAMETERS at the time of database creation and cannot be changed later. Reasoning given in link above is that NLS_DATABASE_PARAMETERS is used by the data dictionary to validate CHECK constraints and changing these parameters could violate those constraints.

NLS_LANGUAGE and NLS_TERRITORY need to be set to the desired values in your parameter file at the time the CREATE DATABASE statement is issued for the values to be stored in NLS_DATABASE_PARAMETERS.

|improve this answer|||||
0

I think you can do simply like

ALTER SYSTEM SET NLS_LANGUAGE='ENGLISH' SCOPE=SPFILE;
ALTER SYSTEM SET NLS_TERRITORY='GERMANY' SCOPE=SPFILE;

Another way would be to change your NLS_LANG environment variable in Windows (or Registry, but I don't know which one takes precedence) and restart the DB.

See NLS_LANGUAGE and NLS_TERRITORY

|improve this answer|||||
  • Yes and no. ALTER SYSTEM changes the NLS-settings for the instance_parameters. So SELECT * FROM nls_instance_parameters; shows that those changes are in effect, but SELECT * FROM nls_database_parameters; stays unchanged. I got a weird application which relies on those database settings, at least their support-agents state that it does. Any ideas except the long way of dropping and recreating the database? To the registry: NLS_LANG is set to GERMAN_GERMANY.WE8MSWIN1252, cause we used a german windows for testing. But nls_database_parameters default to NLS_LANGUAGE=AMERICAN etc. – Fossi Feb 3 '15 at 8:55
  • Strange, you can set NLS_LANGUAGE and NLS_TERRITORY individually on session base. Maybe you can force it with a AFTER LOGON TRIGGER – Wernfried Domscheit Feb 3 '15 at 9:54
  • Indeed, I fear this 3rd party software relies on a direct SELECT FROM nls_database_parameters, I'll consult their support and try to get some clarification. And will try the LOGON TRIGGER while I wait for their response. ;-) Thank you! – Fossi Feb 3 '15 at 11:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for?Browse other questions tagged or ask your own question.