OraFAQ Forum: Server Administration » NLS Settings DB level

Home » RDBMS Server » Server Administration » NLS Settings DB level (oracle 10g)

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

NLS Settings DB level [message #427176]

Wed, 21 October 2009 07:17

praveenc
Messages: 1
Registered: October 2009
Location: Bangalore

Junior Member

Hi,
For making oracle to behave for case insensitive search
How to set NLS parameter in DB level rather session level ?

Thanks and regards
Praveen

Report message to a moderator

Re: NLS Settings DB level [message #427185 is a reply to message #427176]

Wed, 21 October 2009 08:15

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

The only way to do it at database level is to recreate the database as it is set at database creation time.

Regards
Michel

Report message to a moderator

Re: NLS Settings DB level [message #427190 is a reply to message #427185]

Wed, 21 October 2009 08:39

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Are you sure about that ?

You can't change the character set, but other NLS parameters can be set with alter system.

Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SQL> ALTER system SET NLS_COMP=BINARY_CI scope=spfile;

System altered.

SQL>
SQL> alter system set NLS_SORT=BINARY_CI scope=spfile;

System altered.

SQL>

Report message to a moderator

Re: NLS Settings DB level [message #427192 is a reply to message #427190]

Wed, 21 October 2009 08:43

Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Doh! Too much rely on documentation... but is this really taken into account?

Regards
Michel

Report message to a moderator

Re: NLS Settings DB level [message #427193 is a reply to message #427192]

Wed, 21 October 2009 08:46

tahpush
Messages: 961
Registered: August 2006
Location: Stockholm/Sweden

Senior Member

Quote:

NLS_DATABASE_PARAMETERS contains parameters that are "hardcoded" into a given database at its creation time (by copying NLS_INSTANCE_PARAMETERS). They are stored permanently in the Data Dictionary and they cannot be changed in a supported way. The parameters are used mainly to create the NLS environment for evaluation of CHECK constraints. The environment has to be constant so that CHECK conditions never change for given values stored in the database. Otherwise, the constraints could become violated after an init.ora/spfile change.

There may be some other rare situations where some of the NLS_DATABASE_PARAMETERS parameters are used. For example, it seems that language of some error messages generated by server-side Data Pump functionality might be determined by NLS_LANGUAGE value from NLS_DATABASE_PARAMETERS. This would actually be a bug, because NLS_INSTANCE_PARAMETERS are a better source of locale information for such purpose.

Source

Report message to a moderator

Re: NLS Settings DB level [message #427207 is a reply to message #427193]

Wed, 21 October 2009 09:45

ThomasG
Messages: 3212
Registered: April 2005
Location: Heilbronn, Germany

Senior Member

Ah, now it seems clearer.

Here is a FAQ article about the database/instance/session NLS parameters and their priority order.

Report message to a moderator

Previous Topic:	User like SYS
Next Topic:	Export Utility Error in Oracle 9i

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Feb 24 00:00:59 CST 2025