Can you show us the content of your sqlnet.ora file ?
The domain name should be listed there :
NAMES.DEFAULT_DOMAIN = domain.com.xxx
In case you don't have one , create a new sqlnet.ora file using the netca tool.
- after you create that file edit it and add the entry with you domain name!
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> select * from NLS_DATABASE_PARAMETERS where parameter in ('NLS_COMP', 'NLS_SORT');
PARAMETER VALUE
--------------- ---------------
NLS_COMP LINGUISTIC
NLS_SORT BINARY_CI
SQL> select * from NLS_INSTANCE_PARAMETERS where parameter in ('NLS_COMP', 'NLS_SORT');
PARAMETER VALUE
--------------- ---------------
NLS_SORT BINARY_CI
NLS_COMP LINGUISTIC
SQL> create table t1(id varchar2(1), something char(2000));
Table created.
SQL> insert into t1 select 'a', 'a' from dual connect by level <= 10000;
10000 rows created.
SQL> insert into t1 values ('b', 'b');
1 row created.
SQL> commit;
Commit complete.
SQL> create index t1_i1 on t1(id);
Index created.
SQL> exec dbms_stats.gather_table_stats(user, 'T1', cascade=>true, estimate_percent=>100, method_opt=>'for all columns size auto for columns id size 2');
PL/SQL procedure successfully completed.
SQL> select /*+ index(t1 t1_i1) */ count(*) from t1 where id = 'B';
COUNT(*)
----------
1
SQL> 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> create index t1_i2 on t1(NLSSORT("ID",'nls_sort=''BINARY_CI'''));
Index created.
SQL> exec dbms_stats.gather_table_stats(user, 'T1', cascade=>true, estimate_percent=>100, method_opt=>'for all columns size auto for columns id size 2, SYS_NC00003$ size 2');
PL/SQL procedure successfully completed.
SQL> select count(*) from t1 where id = 'B';
COUNT(*)
----------
1
SQL> 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'))
Best Answer
Create a pfile from the spfile:
Edit the generated pfile and remove the parameters, then recreate the spfile from the edited pfile. Bounce the database & all should be well.
The database might need to be down when you recreate the spfile from the pfile.