Correcting error requirements for modifying database nls_length_semantics parameters

1, the developer error requirements

Let's look at a message from a developer who asked a DBA to "modify database nls_length_semantics parameters":

The above mail, for the protection of privacy, the sender, the recipient, the database name is hidden.

The main meaning of the message content is:

(1) The character set of the source and target database is simplified Chinese_china. UTF8, but the value of the source-side database Nls_length_semantics parameter is char, and the value of the target database nls_length_semantics parameter is byte

(2) Understanding of knowledge errors in E-mails : Because the value of the source-side database Nls_length_semantics parameter is char (1 Chinese characters as a byte), the target database nls_length_ The value of the semantics parameter is byte (1 Kanji is 3 bytes), so the source end Varchar2 (16) can store 16 characters, while the target end VARCHAR2 (16) can only save 5 Chinese characters, which causes the data of the source data cannot be inserted into the target database.

(3) the recommended solution to the error in the message: Change the value of the Nls_length_semantics parameter of the target database to the same value as the Nls_length_semantics parameter of the source-side database

2, the carding of knowledge2.1 The purpose of the Nls_length_semantics parameter

The Nls_length_semantics parameter is a byte-and char-two value that is specified for a column that is designed to create char and VARCHAR2 two character types, specifies the length of bytes used, or uses the character length definition, which defaults to byte.

when the parameter is set to BYTE when you define a CHAR column or VARCHAR2 the column is in byte-length mode, and when the parameter is set to CHAR when you define a CHAR column or VARCHAR2 The column takes a character-length approach. This parameter does not have any purpose for columns that already exist in the database, but it is only meaningful when you create a table or modify a table's columns.

the difference between 2.2-byte length and character length

This section from Baidu Library excerpt, the original address is: http://baike.baidu.com/link?url= Gtnaoi4rlzejxtdnisg3z8vm1ipobqab4nv3trsnkh9rwto2er8erkuwuuv00j7invvgpq2o51o-r77sfyiwt_

(1) ASCII code:

An English letter (not case) occupies one byte of space, and a Chinese character occupies two bytes of space. A binary number sequence, which is used as a digital unit in a computer, typically a 8-bit binary number, converted to decimal. The minimum value is 0, and the maximum value is 255. such as an ASCII code is a byte.

( 2 ) UTF-8 Code:

An English character equals one byte, and a Chinese (with traditional) equals three bytes.

(3) Unicode encoding:

One English equals two bytes, and one Chinese (with traditional) equals two bytes.

(4) Symbol:

An English punctuation mark takes up one byte, and a Chinese punctuation mark is two bytes. Example: "." 1 bytes in size, Chinese period ". "Takes up 2 bytes in size.

3. Understanding of knowledge errors in e-mails

The requirement to modify the target-side database Nls_length_semantics parameter in the message is a completely wrong solution, because this developer's understanding of the Nls_length_semantics parameter is incorrect.

The developer, by mistake, nls_length_semantics the parameter, as long as the parameter is changed, all the length types of the columns in the database that involve char and VARCHAR2 two character type have changed.

In fact, the value of the Nls_length_semantics parameter does not have any effect on columns that already exist, except that when you create a column in a table, the default specified column length type is byte or char, and if you specify a length type when you create or modify a table's columns, completely overwrite Nls_ The value of the Length_semantics parameter.

4. Analyze the real cause of the problem

In fact, the real problem for this developer is that the length type of the source-side table field is inconsistent with the type of the target-side table field length.

The root cause of the problem is clear, the solution is easy, the target side table of the field length type to the same as the source end, do not solve the wood. Why modify database parameters also restarts the database.

The use of the Nls_length_semantics parameter is clearly stated in the three CREATE TABLE statements below

(1) Two SQL statements of the specified length type Create table tab_t(t_name varchar2()); Create table tab_t(t_name varchar2(char)); The only difference in the above two statements is that after specifying a column length of 20, the type of the length is specified and the value of the type is different. (2) SQL statements that do not specify a length type Create table tab_t(t_name varchar2()); This statement, after the length of the specified column is 20, does not specify a type of length, what is its type, which is determined by the value of the Nls_length_semantics parameter, which can be set at the session level.

5. Test verification5.1 Confirming 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 a column-length type of byte and test the pluggable data length

(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_semantics BYTE

(2) Create a table with a column length of type 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_used from Dba_ Tab_columns WHERE table_name=' tab_t '

TABLE_NAME column_name data_type char_used --------------------  -----------------------    -------------------------  ----------------------------- tab_t t_name VARCHAR2 B

(4) Insert English string data test

$ export Nls_lang=american_america. UTF8 -note the above, it is important to set the client character set, which can be skipped if the environment variable is set. If a complex character set conversion occurs, a Chinese character may take up to 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, the insertion of three English letters succeeded in inserting a four-letter string, indicating that the actual length is 4, but maximum only 3

(5) Inserting Chinese string data test

1) First Plan "medium" to occupy a few bytes sql> SELECT LENGTHB (' Medium ') from DUAL; LENGTHB (' Medium ') ------------- 3 2) Insert a Chinese character sql> INSERT into tab_t values (' Medium '); 1 row created. 3) Insertion of 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)

Inserting two Chinese characters fails, the actual length is 6, the field maximum is only 3, this validation determines that, under UTF8, a Chinese character occupies 3 characters.

5.3 Change the t_name column of the tab_t table to a char length type and do an 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(3char));

(2) Verify the result of the modification

Sql> selecttable_name,column_name,data_type,char_used from Dba_tab_columns wheretable_name= ' TAB_T ';

TABLE_NAME column_name data_type char_used --------------------  -----------------------    -------------------------  ----------------------------- tab_t t_name VARCHAR2 C

(3) Insertion of two Chinese characters

sql> INSERT into tab_t values (' China '); 1 row created.

VARCHAR2 (3 char) inserted two Chinese characters successfully

6. Summary

After judging the needs of the developers and correcting their understanding of the use errors of the nls_length_semantics parameters, the actual problems faced by modifying the table field length types are solved, the unnecessary database restarts are avoided, and the problems are solved.

This article Li Junjie (Network Name: casing), engaged in "system architecture, operating systems, storage devices, databases, middleware, applications" six levels of systematic performance optimization work

Welcome to the System performance Optimization Professional group, to discuss performance optimization technology together. Group number: 258187244

Correcting error requirements for modifying database nls_length_semantics parameters