I try to create an Oracle 12c Database (Oracle Database 12c Enterprise Edition Release 188.8.131.52.0 - 64bit Production) which ist case insensitive. My first try is just a try to test my aims:
select count(1) from dual where 'a' = 'A'; --0 case sensitive alter session set nls_sort = 'BINARY_CI'; alter session set nls_comp = 'LINGUISTIC'; select count(1) from dual where 'a' = 'A'; --1 --> Case insensitive
alter session works fine but leads to massive problems with tablescans. Oracle ignores all indexes, which didn't created as BINARY_CI / LINGUISTIC.
As a solution for the tablescan problem I try to create a Database, which has the apropriate nls_sort and nls_comp setted as I needed. I started DBCA (Database Configuration Assitent) and created a Database with the two INI parameters:
The Database created but didn't work as expected:
select count(1) from dual where 'a' = 'A'; --0 case sensitive
After some investigation I found:
select * from NLS_DATABASE_PARAMETERS where parameter in ('NLS_COMP', 'NLS_SORT'); /* NLS_COMP BINARY NLS_SORT BINARY */ select * from NLS_INSTANCE_PARAMETERS where parameter in ('NLS_COMP', 'NLS_SORT'); /* NLS_SORT BINARY_CI NLS_COMP LINGUISTIC */
Which means, somehow the NLS Parameter vanished from my Database. I wonder if the reason is the seed Database, which DBCA used as a template. My NLS Parameter were used by the instance but there they have no effect on my query.
Should I create a Database without DBCA? How? ( https://docs.oracle.com/cloud/latest/db121/ADMIN/create.htm#ADMIN11073 ) Or can I change somehow the NLS-Parameter inside the Database?