Oracle 11.2.0.1關於NLS_LENGTH_SEMANTICS的一個Bug

這段時間在做資料移轉，需要從10g的ZHS16GBK字元集的資料庫裡面遷移資料到11g的UTF8字元的資料庫，並且來源資料庫的NLS_LENGTH_SEMANTICS參數值為Byte，目標端的NLS_LENGTH_SEMANTICS參數值為CHAR。如果直接從源使用exp/expdp匯出資料，然後使用imp/impdp匯入資料到目標的話，可能會出現資料超長的情況，這時候就不好搞了，因此我就使用了中轉的方式。即，把來源資料匯入到NLS_LENGTH_SEMANTICS參數值為CHAR的ZHS16GBK字元集的中轉資料庫中，然後再從中轉資料庫中匯出資料並匯入到目標資料庫中。

注：這樣做是有條件的，來源資料庫中沒有長度超過4000/3個中文字元的CHAR/VARCHAR2欄位，原因看這裡

繼續這個問題，之所以這麼做，目的是為了在資料部超長的情況得到CHAR/VARCHAR2類型欄位以CHAR儲存的資料。

根據Oracle的定義，NLS_LENGTH_SEMANTICS參數可以在三個層次生效：資料庫級、執行個體級、會話級，並且生效的優先順序為會話級>執行個體級>資料庫級。

但是，我發現了一個問題：不管把NLS_LENGTH_SEMANTICS在那個層級設定為CHAR，在Oracle 11.2.0.1中，新匯入表都不會使用CHAR做為CHAR/VARCHAR2等資料類型的儲存單位，而是一律使用預設的Byte，除非來源資料就已經指定了CHAR。

請看如下實驗過程，全過程均使用普通使用者，因為NLS_LENGTH_SEMANTICS參數對sys使用者無效：

先查看NLS_LENGTH_SEMANTICS參數，Database、Instance和Session都設定為CHAR
srcbdb@SYS> select * from nls_session_parameters where PARAMETER='NLS_LENGTH_SEMANTICS';

PARAMETER                      VALUE
------------------------------ --------------------------------------------------
NLS_LENGTH_SEMANTICS           CHAR
srcbdb@SYS> select * from nls_instance_parameters where PARAMETER='NLS_LENGTH_SEMANTICS';

PARAMETER                      VALUE
------------------------------ --------------------------------------------------
NLS_LENGTH_SEMANTICS           CHAR
srcbdb@SYS> select * from nls_database_parameters where PARAMETER='NLS_LENGTH_SEMANTICS';

PARAMETER                      VALUE
------------------------------ --------------------------------------------------
NLS_LENGTH_SEMANTICS           CHAR

然後倒入資料

[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, 2009, 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/SEQUENCE
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/COMMENT
Processing object type SCHEMA_EXPORT/FUNCTION/FUNCTION
Processing object type SCHEMA_EXPORT/PROCEDURE/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

查看錶結構
[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, 2009, Oracle.  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

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
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 MONEY_TYPE                                                                                                          VARCHAR2(3)                      <--------------   這說明表是以Byte儲存VARCHAR2資料類型的。
 TO_RMB_RATE                                                                                                         NUMBER(15,7)
 TO_USD_RATE                                                                                                         NUMBER(15,7)

我們再建立一個表看一下，注意，不顯示指定CHAR或者Byte，資料使用預設值！

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

Table created.

srcbdb@VIP> desc lzb
 Name                                                                                                              Null?    Type
 ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
 COL1                                                                                                                CHAR(10 CHAR)                   <--------------   這時候發現已經自動使用了CHAR。
 COL2                                                                                                                VARCHAR2(10 CHAR)

結論：在11.2.0.1裡面，對於使用imp/impdp工具匯入的資料，nls_length_semantics不生效！

我不知道這是不是Oracle的Bug，看官也可以幫我求證一下！！！！

【附錄：MOS上的文章】

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