Oracle 参数 NLS_LENGTH_SEMANTICS 官方解释，作用，如何配置最优化建议

本站中文解释

NLS_LENGTH_SEMANTICS 是用于指定列类型为CHAR类型时，字符长度的单位，即规定CHAR(n)表示的意思。其有两种设置：

CHAR： 即CHAR(n)代表char类型能够包含n个字符；

BYTE： 即CHAR(n)代表char类型能够包含n个字节。

正确设置：

设置NLS_LENGTH_SEMANTICS为CHAR或BYTE,如“alter system set NLS_LENGTH_SEMANTICS=CHAR scope=both;”可以将当前数据库对于CHAR，VARCHAR，VARCAP的配置改为按字符个数来指定；数据库管理人员在定义表的时候，如果指定CHAR类型的时候可以因NLS_LENGTH_SEMANTICS设置不同而发生误解，因此建议我们在定义表时尽量使用指定字符数量的CHAR，比如用CHAR(200 CHAR)而不是CHAR(200 BYTE)来指定。

官方英文解释

NLS_LENGTH_SEMANTICS is used to specify length semantics.

The session-level value of NLS_LENGTH_SEMANTICS specifies the default length semantics to use for VARCHAR2 and CHAR table columns, user-defined object attributes, and PL/SQL variables in database objects created in the session. This default may be overridden by the explicit length semantics qualifiers BYTE and CHAR in column, attribute, and variable definitions.

The instance-level value of NLS_LENGTH_SEMANTICS provides a default for the session-level value if NLS_LENGTH_SEMANTICS is not set explicitly by the database client through the NLS_LENGTH_SEMANTICS client environment variable (does not apply to JDBC Thin clients), or the ALTER SESSION SET NLS_LENGTH_SEMANTICS statement.

NCHAR, NVARCHAR2, CLOB, and NCLOB columns are always character-based.

Sessions logged in as SYS do not use the NLS_LENGTH_SEMANTICS parameter. They use BYTE length semantics for all created objects unless overridden by the explicit BYTE and CHAR qualifiers in object definitions (SQL DDL statements).

Note:

Oracle strongly recommends that you do NOT set the NLS_LENGTH_SEMANTICS parameter to CHAR in the instance or server parameter file. This may cause many existing installation scripts to unexpectedly create columns with character length semantics, resulting in run-time errors, including buffer overflows.

See Also:

Oracle Database
Globalization Support Guide for more information about this parameter