我在哪里可以查询 Oracle 数据库的区分大小写？

Where can I query an oracle databases' case-sensitivity?

提问by thecoop

Where can I query the current case-sensitivity setting of an oracle database?

哪里可以查询oracle数据库当前的区分大小写设置？

I've tried looking in v$database, nls_database_parameters, and looking through the system packages, but none of them seem to provide the information I need...

我试过查看v$database、查看nls_database_parameters和查看系统包，但它们似乎都没有提供我需要的信息......

回答by Quassnoi

In Oracle 10gR2:

在Oracle 10gR2：

SELECT  *
FROM    NLS_SESSION_PARAMETERS
WHERE   parameter IN ('NLS_COMP', 'NLS_SORT')

SQL> ALTER SESSION SET NLS_COMP = 'LINGUISTIC'
  2  /

Session altered
SQL> SELECT  COUNT(*)
  2  FROM    dual
  3  WHERE   'a' = 'A'
  4  /

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

SQL> ALTER SESSION SET NLS_COMP = 'BINARY'
  2  /

Session altered
SQL> SELECT  COUNT(*)
  2  FROM    dual
  3  WHERE   'a' = 'A'
  4  /

  COUNT(*)
----------
         0

From documentation:

从文档：

NLS_COMPspecifies the collation behavior of the database session.

Values:

• BINARY

  Normally, comparisons in the WHEREclause and in PL/SQLblocks is binary unless you specify the NLSSORTfunction.

• LINGUISTIC

  Comparisons for all SQLoperations in the WHEREclause and in PL/SQLblocks should use the linguistic sort specified in the NLS_SORTparameter. To improve the performance, you can also define a linguistic index on the column for which you want linguistic comparisons.

• ANSI

  A setting of ANSIis for backwards compatibility; in general, you should set NLS_COMPto LINGUISTIC

NLS_COMP指定数据库会话的整理行为。

价值观：

• BINARY

  通常，除非指定函数，否则WHERE子句和PL/SQL块中的比较是二进制的NLSSORT。

• LINGUISTIC

  子句中和块中所有SQL操作的比较应使用参数中指定的语言排序。为了提高性能，您还可以在要进行语言比较的列上定义语言索引。WHEREPL/SQLNLS_SORT

• ANSI

  设置ANSI是为了向后兼容；一般来说，你应该设置NLS_COMP为LINGUISTIC

回答by user314847

In addition to the answers already given be aware that case sensitivity changes in 11g - e.g. see the 11g documentation re passwords.

除了已经给出的答案之外，请注意 11g 中区分大小写的变化 - 例如，请参阅 11g 文档重新密码。

回答by spencer7593

For Oracle 10gR2 (and later), the parameters are NLS_COMP and NLS_SORT.

对于 Oracle 10gR2（及更高版本），参数为 NLS_COMP 和 NLS_SORT。

select * from v$nls_parameters where parameter in ('NLS_COMP','NLS_SORT');

(These parameters are set at the session level. The settings for a session are inherited from the database setting, unless overridden by setting an OS environment variable, or an ALTER SESSION statement.)

（这些参数在会话级别设置。会话的设置继承自数据库设置，除非通过设置操作系统环境变量或 ALTER SESSION 语句覆盖。）

If you want "case-insensitive" sorting and string matching, you can try these settings:

如果您想要“不区分大小写”的排序和字符串匹配，您可以尝试以下设置：

alter session set NLS_SORT=BINARY_CI;
alter session set NLS_COMP=LINGUISTIC;

Those aren't the only settings for the parameters, of course. Oracle 10gR2 documentation:

当然，这些并不是参数的唯一设置。Oracle 10gR2 文档：

10gR2 Linguistic Sorting and String Searching

10gR2 语言排序和字符串搜索