oracle varchar2 字元儲存長度問題

 問題描述：

Oracle源表中可存放“中文測試”4個漢字，但經過PowerCenter抽取寫入同結構目標表後，中文被截斷為2個漢字“中文”。

CREATE TABLE 源表

(

  A  VARCHAR2(4 CHAR)

)

 

CREATE TABLE 目標表

(

  A  VARCHAR2(4 CHAR)

)

 

解決方案：

該問題是由NLS_LENGTH_SEMANTICS=BYTE所致，須將其設定為CHAR。

 

如：為Integration Service添加 Environment variables:

Name: NLS_LENGTH_SEMANTICS

Value: CHAR

 

系統內容：
作業系統：Window Server 2003 SP2
中介軟體：WebSphere Platform 6.1
資料庫：Oracle 10.2.0.1.0
字元集：NLS_CHARACTERSET UTF8
Maximo版本號碼：V7110-890

問題現象：
 1、在遷移Maximo資料庫配置資訊時，在執行類似語句時：
 insert into wfassignment@orcl57 select * from wfassignment t where t.processname=pc_row.processname and t.processrev=pc_row.processrev;
 總是報異常：description 實際長度是106，而目標表中的description欄位長度為100，超出最大長度。

 2、結果在目標中檢查欄位：wfassignment.description varchar2(100) 只能插入約33個中文字加一個英文字；
 一個中文字佔三個位元組，一個英文字佔一個位元組。
 而原資料庫wfassignment.description中可以插入100個漢字。

 3、用lengthb(description), length(description)查詢欄位長度，
  在原庫中如果欄位值都是漢字，則查詢結果lengthb是length的三倍，
  在新庫中同樣的欄位值，則查詢結果長度一樣。

問題原因分析：
 1、資料庫字元集選擇的是NLS_CHARACTERSET=UTF8，如果NLS_CHARACTERSET=ZHS16GBK就不會有這種情況；

 2、原庫中NLS_LENGTH_SEMANTICS=CHAR，新庫中NLS_LENGTH_SEMANTICS=BYTE；

 3、主要是第2條造成的，如果字元集是UTF8，欄位類型VARCHAR2中一個字元占幾個位元組是由NLS_LENGTH_SEMANTICS參數決定的，
    如果是BYTE，則一個字元佔一個位元組，如果是CHAR，則一個字元佔四個位元組。
    通過下面的語句可以查看實際的data_length長度
    select table_name, column_name, data_type, data_length from cols

 4、查看資料庫字元集參數的參數表是：v$nls_parameters，與字元集相關參數都在此表中；
  也可以用下面的語句查詢；
  select * from v$parameter where name = 'nls_length_semantics'
  國家字元集：NLS_NCHAR_CHARACTERSET=AL16UTF16
  資料庫字元集：NLS_CHARACTERSET=AL32UTF8

解決方案：
 一、修改目標資料庫的"nls_length_semantics"屬性值為"CHAR"
  
  1、執行：alter system set nls_length_semantics = CHAR scope=both;
   這種方式修改後重啟資料庫執行個體後，再查詢時值又是BYTE了；
  2、修改檔案：{installpath}/oracle/product/10.2.0/admin/orcl/pfile/init.ora.622010124846
   添加下面一段：
   ###########################################
   # NLS
   ###########################################
   nls_language="SIMPLIFIED CHINESE"
   nls_length_semantics=CHAR
   nls_territory="CHINA"

   這種方式修改後重啟資料庫執行個體後，再查詢時值又是BYTE了；
  3、重啟伺服器電腦，然後查詢時值是CHAR了，沒有找到原因為什麼重啟電腦可以了。

 二、欄位description還是不能插入100個漢字
  1、雖然nls_length_semantics已經改為CHAR了，當目標庫中表欄位description varchar2(100)還是
   不能插入100個漢字
  
  2、查詢cols表，發現data_length長度還是100，再看wfassignment.description
   欄位定義對象，探索資料庫類型變為：varchar2(100 byte)
   
  3、alter table wfassignment modify description VARCHAR2(100); 問題解決；
   查詢cols表，發現data_length長度是400了，可以插入100個漢字了。

  4、執行下面的語句，把產生的指令碼儲存為一個sql檔案，用PL/SQL工具執行，就可以更新所有字元欄位：
   select 'alter table '||table_name||' modify '||column_name||' VARCHAR2('||data_length||');'
   from cols where data_type = 'VARCHAR2' and table_name in
   (select table_name from tabs where status = 'VALID')

 三、如果是在Maximo產品中，因為欄位wfassignment.description varchar2(100)都是全文檢索索引欄位，
  不能夠直接修改欄位長度，要求刪除全文檢索索引索引，處理方法見《刪除Maximo中的所有全文檢索索引索引

 

 

轉載http://blog.csdn.net/lanyunit/article/details/5768581

 

 select * from v$nls_parameters

UPDATE PROPS$ SET NLS_CHARACTERSET='UTF8'
用戶端的nls_lang也設為UTF8

 

http://www.eygle.com/index-special.htm oracle 資料恢複等