Home » RDBMS Server » Server Administration » NLS Settings DB level (oracle 10g)
NLS Settings DB level [message #427176] Wed, 21 October 2009 07:17 Go to next message
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
Re: NLS Settings DB level [message #427185 is a reply to message #427176] Wed, 21 October 2009 08:15 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
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

Re: NLS Settings DB level [message #427190 is a reply to message #427185] Wed, 21 October 2009 08:39 Go to previous messageGo to next message
ThomasG
Messages: 3184
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>



Re: NLS Settings DB level [message #427192 is a reply to message #427190] Wed, 21 October 2009 08:43 Go to previous messageGo to next message
Michel Cadot
Messages: 63806
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Doh! Too much rely on documentation... but is this really taken into account?

Regards
Michel
Re: NLS Settings DB level [message #427193 is a reply to message #427192] Wed, 21 October 2009 08:46 Go to previous messageGo to next message
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
Re: NLS Settings DB level [message #427207 is a reply to message #427193] Wed, 21 October 2009 09:45 Go to previous message
ThomasG
Messages: 3184
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.
Previous Topic: User like SYS
Next Topic: Export Utility Error in Oracle 9i
Goto Forum:
  


Current Time: Tue Sep 27 07:34:54 CDT 2016

Total time taken to generate the page: 0.11674 seconds