Oracle: Creating Case Insensitive Database with NLS_COMP and NLS_SORT I try to create an Oracle 12c Database (Oracle Database 12c Enterprise Edition Release 12.1.0.2.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 --&gt; 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:

nls_comp=LINGUISTIC
nls_sort=BINARY_CI


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?

Regards
  Dezsö
Even if you manage to create a database with nls_comp=LINGUISTIC, nls_sort=BINARY_CI (from scratch (Custom Database or manually), not seed or template) , the indexes will be still created without these settings and you have to take specify them manually.

SQL&gt; select * from NLS_DATABASE_PARAMETERS where parameter in ('NLS_COMP', 'NLS_SORT');

PARAMETER       VALUE
--------------- ---------------
NLS_COMP        LINGUISTIC
NLS_SORT        BINARY_CI

SQL&gt; select * from NLS_INSTANCE_PARAMETERS where parameter in ('NLS_COMP', 'NLS_SORT');

PARAMETER       VALUE
--------------- ---------------
NLS_SORT        BINARY_CI
NLS_COMP        LINGUISTIC

SQL&gt; create table t1(id varchar2(1), something char(2000));

Table created.

SQL&gt; insert into t1 select 'a', 'a' from dual connect by level &lt;= 10000;

10000 rows created.

SQL&gt; insert into t1 values ('b', 'b');

1 row created.

SQL&gt; commit;

Commit complete.

SQL&gt; create index t1_i1 on t1(id);

Index created.

SQL&gt; exec dbms_stats.gather_table_stats(user, 'T1', cascade=&gt;true, estimate_percent=&gt;100, method_opt=&gt;'for all columns size auto for columns id size 2');

PL/SQL procedure successfully completed.

SQL&gt; select /*+ index(t1 t1_i1) */ count(*) from t1 where id = 'B';

  COUNT(*)
----------
         1

SQL&gt; select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  34cypvvhnwczh, child number 0
-------------------------------------
select /*+ index(t1 t1_i1) */ count(*) from t1 where id = 'B'

Plan hash value: 3724264953

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   907 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |     2 |            |          |
|*  2 |   TABLE ACCESS FULL| T1   |  5001 | 10002 |   907   (1)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(NLSSORT("ID",'nls_sort=''BINARY_CI''')=HEXTORAW('6200'))


Index can not be used, hint does not help, and the estimated cardinality is just simply CEIL (NUM_ROWS / NUM_DISTINCT). This time with the proper index:

SQL&gt; create index t1_i2 on t1(NLSSORT("ID",'nls_sort=''BINARY_CI'''));

Index created.

SQL&gt; exec dbms_stats.gather_table_stats(user, 'T1', cascade=&gt;true, estimate_percent=&gt;100, method_opt=&gt;'for all columns size auto for columns id size 2, SYS_NC00003$ size 2');

PL/SQL procedure successfully completed.

SQL&gt; select count(*) from t1 where id = 'B';

  COUNT(*)
----------
         1

SQL&gt; select * from table(dbms_xplan.display_cursor);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  9pvkbfkycz6q4, child number 0
-------------------------------------
select count(*) from t1 where id = 'B'

Plan hash value: 3225262789

---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |       |       |     1 (100)|          |
|   1 |  SORT AGGREGATE   |       |     1 |     3 |            |          |
|*  2 |   INDEX RANGE SCAN| T1_I2 |     1 |     3 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."SYS_NC00003$"=HEXTORAW('6200'))