Correct the request for modifying the NLS_LENGTH_SEMANTICS parameter of the database. nlslengthsemantics

Source: Internet
Author: User

Correct the request for modifying the NLS_LENGTH_SEMANTICS parameter of the database. nlslengthsemantics
1. developer error requirements

First, let's look at an email that a developer asked a DBA to modify the NLS_LENGTH_SEMANTICS parameter of the database and restart the database:

In the preceding email, for privacy protection, the sender, recipient, and database name are implicitly coated.

The email content mainly means:

(1) the character set of the source and target databases is SIMPLIFIED CHINESE_CHINA.UTF8, but the value of the NLS_LENGTH_SEMANTICS parameter of the source database is char, and the value of the NLS_LENGTH_SEMANTICS parameter of the target database is byte

(2)Understanding of Knowledge errors in emails: Because the value of the NLS_LENGTH_SEMANTICS parameter in the source database is char (one Chinese character is considered as one byte), the value of the NLS_LENGTH_SEMANTICS parameter in the target database is byte (one Chinese Character occupies three bytes ), therefore, the source Varchar2 (16) can store 16 Chinese characters, while the target Varchar2 (16) can only store 5 Chinese characters, as a result, the source data cannot be inserted into the target database.

(3)Solution to errors in emails: Change the value of the NLS_LENGTH_SEMANTICS parameter of the target database to the same value as that of the source database NLS_LENGTH_SEMANTICS.

2. Sorting knowledge 2.1 use of the NLS_LENGTH_SEMANTICS Parameter

The NLS_LENGTH_SEMANTICS parameter specifies the byte length when creating a CHAR and VARCHAR2 character-type column, or uses the character length definition method, which has two values: byte and char, the default value is byte.

When this parameter is set to BYTE, the CHAR or VARCHAR2 columns are defined in the BYTE length mode. When this parameter is set to CHAR, the CHAR or VARCHAR2 columns are defined in the character length mode. This parameter is meaningless only when creating a table or modifying a table column.

Difference between the length of 2.2 bytes and the length of Characters

This chapter from Baidu Library excerpt, original address: http://baike.baidu.com/link? Url = gtnaOI4rLZejxtdNISG3z8Vm1IpobqAB4nv3TRSnKh9RwTo2eR8eRkUWUUv00J7INVvGPQ2O51o-r77SfyIwT _

(1) ASCII code:

An English letter (case-insensitive) occupies the space of one byte, and a Chinese character occupies the space of two bytes. A sequence of binary numbers. It is generally an 8-bit binary number used as a numerical unit in a computer and converted to decimal. The minimum value is 0 and the maximum value is 255. For example, an ASCII code is a byte.

(2) UTF-8 Encoding:

An English character is equal to one byte, and a Chinese character (including traditional Chinese characters) is equal to three bytes.

(3) Unicode encoding:

An English is equal to two bytes, and a Chinese (including traditional Chinese) is equal to two bytes.

(4) symbol:

An English Punctuation occupies one byte, and a Chinese Punctuation occupies two bytes. For example, the English period "." occupies the size of 1 byte and the Chinese period "." The size of 2 bytes.

3. Understanding of Knowledge errors in emails

Modifying the NLS_LENGTH_SEMANTICS parameter of the target database in the email is a completely incorrect solution. The reason for this is that the developer's understanding of the NLS_LENGTH_SEMANTICS parameter is incorrect.

The developer mistakenly interpreted the NLS_LENGTH_SEMANTICS parameter as long as the parameter is changed, the length types of all the columns involved in the CHAR and VARCHAR2 types in the database have changed.

Actually, it is not the value of the NLS_LENGTH_SEMANTICS parameter. It does not affect existing columns. However, when creating columns in a table, the default column length type is byte or char, if the length type is specified when you create or modify a table column, the value of the NLS_LENGTH_SEMANTICS parameter is completely overwritten.

4. Analyze the real cause of the problem

In fact, the real cause of this problem is that the source table field length type is inconsistent with the target table field length type.

The root cause of the problem is clarified, and the solution is easy. Changing the field length type of the target table to the same as that of the source table will not solve the problem. Why modify Database parameters and restart the database.

The following three create table statements clearly describe the usage of the NLS_LENGTH_SEMANTICS parameter.

(1) Two SQL statements of the specified length type

Create table tab_t (t_name varchar2 (20 byte ));

Create table tab_t (t_name varchar2 (20 char ));

The only difference between the preceding two statements is that after the specified column length is 20, the length type is specified. The type value is different.

(2) SQL statements with no Length Specified

Create table tab_t (t_name varchar2 (20 ));

This statement does not specify the length type after the length of the specified column is 20. What is the type of the column? This is determined by the value of the NLS_LENGTH_SEMANTICS parameter, the parameter value can be set at the session level.

5. test and verification 5.1 confirm the character set type of the database

SQL> select * from nls_database_parameters t where t. parameter = 'nls _ CHARACTERSET ';

PARAMETER VALUE

--------------------------------------------------------------------

NLS_CHARACTERSET AL32UTF8

 

5.2 create a table with the column length type of byte and test the data length that can be inserted

(1) view the current value of the NLS_LENGTH_SEMANTICS Parameter

SQL> selectname, value from v $ parameter where upper (name) = 'nls _ LENGTH_SEMANTICS ';

NAME VALUE

-------------------------------------------------------------

Nls_length_semanticsBYTE

 

(2) create a table with the column length type of byte

SQL> create table tab_t (t_name varchar2 (3 ));

(3) view the t_name column length type of the newly created tab_t table

SQL> select table_name, column_name, data_type, char_usedfrom dba_tab_columnswhere table_name = 'tab _ t'

TABLE_NAME COLUMN_NAME DATA_TYPECHAR_USED

-------------------------------------------------------------------------------------------------

TAB_T T_NAME VARCHAR2B

(4) Test the insertion of English string data

$ Export NLS_LANG = AMERICAN_AMERICA.UTF8

-- Note that setting the client character set is important. skip this step if the environment variable is set. If complex character set conversion occurs, a Chinese character may occupy 6 bytes.

 

SQL> insert into tab_t values ('zho ');

1 row created.

 

SQL> insert into tab_t values ('zhon ');

Insert into tab_t values ('zhon ')

*

ERROR at line 1:

ORA-12899: value too large for column "SYS". "TAB_T". "T_NAME" (actual: 4, maximum: 3)

From the test data above, we can see that the three English letters are successfully inserted, and the four-letter string fails to be inserted, indicating that the actual length is 4, but maximum only 3

(5) Test the function of inserting a Chinese String

1) Plan the number of bytes occupied by the word "medium"

SQL> SELECT LENGTHB ('中') FROM DUAL;

LENGTHB ('zhong ')

-------------

3

2) Insert a Chinese character

SQL> insert into tab_t values ('中 ');

1 row created.

 

3) insert two Chinese characters

SQL> insert into tab_t values ('China ');

Insert into tab_t values ('China ')

*

ERROR at line 1:

ORA-12899: value too large for column "SYS". "TAB_T". "T_NAME" (actual: 6, maximum: 3)

An error occurred while inserting two Chinese characters. The actual length is 6 and the field maximum is only 3. Verify that at UTF8, one Chinese Character occupies three characters.

 

5.3 change the t_name column of the tab_t table to the char length type and perform the insert length test.

(1) Change the t_name column length type of the tab_t table to char.

SQL> alter table tab_t modify (t_name varchar2 (3 char ));

(2) Verify the Modification result

SQL> selecttable_name, column_name, data_type, char_used from dba_tab_columns wheretable_name = 'tab _ T ';

TABLE_NAME COLUMN_NAME DATA_TYPECHAR_USED

-------------------------------------------------------------------------------------------------

TAB_T T_NAME VARCHAR2C

(3) Insert two Chinese characters

SQL> insert into tab_t values ('China ');

 

1 row created.

Varchar2 (3 char) successfully inserted two Chinese characters

 

6. Summary

After judging the requirements of developers and correcting their understanding of the invalid use of the NLS_LENGTH_SEMANTICS parameter, we can modify the table field length type to solve their actual problems, this avoids an unnecessary database restart and solves the problem.

 

 

Author: LI Junjie (Network Name: Step-by-Step), engaged in "system architecture, operating system, storage device, database, middleware, application" six levels of systematic performance optimization work

Join the system performance optimization professional group to discuss performance optimization technologies. GROUP: 258187244

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.