Oracle 11.2.0.1: a Bug in NLS_LENGTH_SEMANTICS

During this period of time, data needs to be migrated from the database of the 10g ZHS16GBK character set to the database of the 11g UTF8 character set, and the value of the NLS_LENGTH_SEMANTICS parameter of the source database is Byte, the value of the target NLS_LENGTH_SEMANTICS parameter is CHAR. If you use exp/expdp to export data from the source directly and then use imp/impdp to import data to the target, the data may be too long, and this may be difficult, so I used the transfer method. That is, import the source data to the intermediate database of the ZHS16GBK character set whose NLS_LENGTH_SEMANTICS parameter value is CHAR, then export the data from the intermediate database and import the data to the target database.

Note: This is a condition. The source database does not have a CHAR/VARCHAR2 field with a length of more than 4000/3 Chinese characters. The reason is as follows:

Continue with this problem,PurposeIt is used to obtain the CHAR/VARCHAR2 fields stored as CHAR when the data is too long.

According to Oracle definition, the NLS_LENGTH_SEMANTICS parameter can take effect at three levels: database level, instance level, and session level. The priority of the parameter takes effect at the session level> instance level> database level.

However, I found a problem: No matter which level the NLS_LENGTH_SEMANTICS is set to CHAR11.2.0.1Medium, newImportTable allCHAR is not usedIt is a unit of storage for CHAR/VARCHAR2 and other data types,Use the default ByteUnless CHAR is specified for the source data.

See the following experiment. Common users are used throughout the entire process, because the NLS_LENGTH_SEMANTICS parameter is invalid for sys users:

First, check the NLS_LENGTH_SEMANTICS parameter. Set Database, Instance, and Session to CHAR.
Srcbdb @ SYS> select * from nls_session_parameters where PARAMETER = 'nls _ LENGTH_SEMANTICS ';

PARAMETER VALUE
--------------------------------------------------------------------------------
NLS_LENGTH_SEMANTICSCHAR
Srcbdb @ SYS> select * from nls_instance_parameters where PARAMETER = 'nls _ LENGTH_SEMANTICS ';

PARAMETER VALUE
--------------------------------------------------------------------------------
NLS_LENGTH_SEMANTICSCHAR
Srcbdb @ SYS> select * from nls_database_parameters where PARAMETER = 'nls _ LENGTH_SEMANTICS ';

PARAMETER VALUE
--------------------------------------------------------------------------------
NLS_LENGTH_SEMANTICSCHAR

Then, import the data.

[Oracle @ instsvr1 dumpdp] $ impdp vip/vip directory = dumpdir dumpfile = vip. byte. dat logfile = $ ORACLE_SID.vip.impdp. 'date + '% Y % m % d % H % M % s ''. log

Import: Release 11.2.0.1.0-Production on Wed Mar 7 16:21:03 2012

Copyright (c) 1982,200 9, Oracle and/or its affiliates. All rights reserved.

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
And Real Application Testing options
Master table "VIP". "SYS_IMPORT_FULL_01" successfully loaded/unloaded
Starting "VIP". "SYS_IMPORT_FULL_01": vip/******** directory = dumpdir dumpfile = vip. byte. dat logfile = srcbdb. vip. impdp.20120307162103.log
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/DB_LINK
ORA-31685: Object type DB_LINK: "VIP". "DB_SRCBDATA" failed due to insufficient privileges. Failing SQL is:
Create database link "DB_SRCBDATA" connect to "SRCBDATA" identified by values '053e86648883623a93807ead3c7a2ea776fa8f916a941afb2e 'using' srcbfin'
 
Processing object type SCHEMA_EXPORT/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE
Processing object type SCHEMA_EXPORT/FUNCTION/ALTER_FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/ALTER_PROCEDURE
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "VIP". "SYS_IMPORT_FULL_01" completed with 1 error (s) at 16:24:48

View table structure
[Oracle @ instsvr1 dumpdp] $ sqlplus vip/vip

SQL * Plus: Release 11.2.0.1.0 Production on Wed Mar 7 14:56:04 2012

Copyright (c) 1982,200 9, Oracle. All rights reserved.


Connected:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0-64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
And Real Application Testing options

Srcbdb @ VIP> select * from tab where rownum <10;

TNAME TABTYPE CLUSTERID
------------------------------------------------------------------------------------
DISCARD_TBDSCLIENT TABLE
IT_AJDK TABLE
IT_DSDK TABLE
IT_DSDQCK TABLE
IT_DSHQCK TABLE
IT_DSKH TABLE
SNAPTBEXCHANGERATE TABLE
TBADVICELOG TABLE
TBADVICELOGSETMEAL TABLE

9 rows selected.

Srcbdb @ VIP> desc SNAPTBEXCHANGERATE
Name Null? Type
Certificate -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
MONEY_TYPEVARCHAR2 (3)<-------------- This indicates that the table stores the VARCHAR2 data type in bytes.
TO_ RMB _RATE NUMBER (15, 7)
TO_USD_RATE NUMBER (15, 7)

Let's try againNewTake a look at a table. Note that the specified CHAR or Byte is not displayed. The default value is used for data!

Srcbdb @ VIP> create table lzb (col1 char (10), col2 varchar2 (10 ));

Table created.

Srcbdb @ VIP> desc lzb
Name Null? Type
Certificate -----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
COL1 CHAR (10 CHAR)<-------------- CHAR is automatically used.
COL2 VARCHAR2 (10 CHAR)

Conclusion:In 11.2.0.1, nls_length_semantics does not take effect for data imported using the imp/impdp tool!

I don't know if this is an Oracle Bug. You can verify it for me !!!!

[Appendix: articles on MOS]

Examples and limits of BYTE and CHAR semantics usage (NLS_LENGTH_SEMANTICS) [ID 144808.1]