I searched in the Forum/Google/etc,... but unfortunaly I don't found a solution.
For our Application we need an case insenstive search. So if we set the change the Session Parameters to NLS_COMP=LINGUISTIC and NLS_SORT=BINARY_CI it works fine. Now set this two Parmeters in the NLS_INSTANCE_PARAMETERS.
Now when I start SQLPLUS and check the NLS_SESSION_PAREMTERS they NLS_COMP is correct but the NLS_SORT is set to NLS_SORT=GERMAN .
Is there are way to force this Paremters from INSTANCE so that every Client always use the Parameter from NLS_INSTANCE_PARAMETERS?
Due that it's not an own Application we can't use ALTER SESSION SET .... .
You can setup this in a logon trigger. You can check the user name and setup the session parameters for the application user only.
there is an example here e.g. http://www.dba-oracle.com/oracle_tips_ault_custom_parameters_users.htm
or even a better here: http://psoug.org/reference/system_trigger.html
BTW if you application uses a connection pool it has a feature to setup session parameters or auto execute an sql script to setup session parameters next to the login information...
You could define the parameters as environment variables. If not explicitly set, NLS_SORT is derived from NLS_LANGUAGE and NLS_LANGUAGE (if also not explicitly set) is derived from NLS_LANG.
So your settings in pfile/spile get always overwritten, when you start a sqlplus session.
It depends on how the application connects to the database. If it uses OCI directly or through ODBC (and, I suppose, ODP .NET), setting NLS_SORT and NLS_COMP as environment variables is the easiest method. If the application connects through JDBC Thin, you have to use the logon trigger. Neither environment variables nor the logon trigger will work for NLS_SORT with JDBC OCI (though both should work for NLS_COMP with JDBC OCI).
Te solution works fine but after implementing i run into a performance Problem. I can't say detailed information about the Performance issue because I don't have the knowledge. I only can say that after more ore less one day the Application is very slow when they access the database.
Does anybody has an idea how I can improve the Performance from this Trigger?
Setting NLS_COMP=LINGUISTIC and NLS_SORT=BINARY_CI affects all relational operators in all SQL executed by affected sessions. This is why it is usually not a good idea. In most cases, the performance issues are caused by the fact that normal indexes cannot be used in processing of linguistic comparisons. You need to redefine them as functional indexes on NLSSORT(column,'NLS_SORT=BINARY_CI').
In general, applications should be modified to be more "fine-grained" in their use of linguistic operations. There is no better solution yet.