oracle - 在哪里可以查询oracle数据库的大小写敏感度？

Question

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

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

Answer 1

在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

来自documentation :

NLS_COMP specifies the collation behavior of the database session.

Values:

• BINARY

  Normally, comparisons in the WHERE clause and in PL/SQL blocks is binary unless you specify the NLSSORT function.

• LINGUISTIC

  Comparisons for all SQL operations in the WHERE clause and in PL/SQL blocks should use the linguistic sort specified in the NLS_SORT parameter. To improve the performance, you can also define a linguistic index on the column for which you want linguistic comparisons.

• ANSI

  A setting of ANSI is for backwards compatibility; in general, you should set NLS_COMP to LINGUISTIC