oracle字符集的更改【轉】

47328983發表於2011-05-04
Oracle

oracle字符集的更改

關鍵字: jinguo

2． 字符集的更改

 

資料庫建立以後，如果需要修改字符集，通常需要重建資料庫，通過匯入匯出的方式來轉換。
我們也可以通過以下方式更改



ALTER DATABASE CHARACTER SET 




注意:修改資料庫字符集時必須謹慎，修改之前一定要為資料庫備份。由於不能回退這項操作,因此可能會造成資料丟失或者損壞。

這是最簡單的轉換字符集的方式，但並不總是有效。
這個命令在Oracle8時被引入Oracle,這個操作在本質上並不轉換任何資料庫字元，只是簡單的更新資料庫中所有跟字符集相關的資訊。


這意味著，你只能在新字符集是舊字符集嚴格超集的情況下使用這種方式轉換。
所謂超集是指:
當前字符集中的每一個字元在新字符集中都可以表示，並使用同樣的程式碼點
比如很多字符集都是US7ASCII的嚴格超集。


如果不是超集，將獲得以下錯誤:







SQL> ALTER DATABASE CHARACTER SET  ZHS16CGB231280;
ALTER DATABASE CHARACTER SET  ZHS16CGB231280
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set



下面我們來看一個測試(以下測試在Oracle9.2.0下進行，Oracle9i較Oracle8i在編碼方面有較大改變，在Oracle8i中，測試結果可能略有不同): 







SQL> select name,value$ from props$ where name like '%NLS%';

NAME                           VALUE$
------------------------------ ------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               US7ASCII
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
……………….
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              9.2.0.4.0

20 rows selected.
SQL> select name,dump(name) from eygle.test;

NAME   DUMP(NAME)
------------------------------------------------------
測試     Typ=1 Len=4: 178,226,202,212
Test      Typ=1 Len=4: 116,101,115,116


2 rows selected.



轉換字符集，資料庫應該在RESTRICTED模式下進行.








 
c:\>sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.4.0 - Production on Sat Nov 1 10:52:30 2003

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.4.0 - Production

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT;
ORACLE instance started.

Total System Global Area   76619308 bytes
Fixed Size                   454188 bytes
Variable Size              58720256 bytes
Database Buffers           16777216 bytes
Redo Buffers                 667648 bytes
Database mounted.
SQL> ALTER SESSION SET SQL_TRACE=TRUE;

Session altered.

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

System altered.

SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

System altered.

SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;

System altered.

SQL> ALTER DATABASE OPEN;

Database altered.

SQL> set linesize 120
SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;
ALTER DATABASE CHARACTER SET ZHS16GBK
*
ERROR at line 1:
ORA-12721: operation cannot execute when other sessions are active


SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;
ALTER DATABASE CHARACTER SET ZHS16GBK
*
ERROR at line 1:
ORA-12716: Cannot ALTER DATABASE CHARACTER SET when CLOB data exists

在Oracle9i中，如果資料庫存在CLOB型別欄位，那麼就不允許對字符集進行轉換

SQL>
    
     


這時候，我們可以去檢視alert.log日誌檔案，看CLOB欄位存在於哪些表上:








ALTER DATABASE CHARACTER SET ZHS16GBK
 SYS.METASTYLESHEET (STYLESHEET) - CLOB populated
ORA-12716 signalled during: ALTER DATABASE CHARACTER SET ZHS16GBK...


對於不同情況，Oracle提供不同的解決方案，如果是使用者資料表，一般我們可以把包含CLOB欄位的表匯出，然後drop掉相關物件，
轉換後再匯入資料庫；對於系統表，可以按照以下方式處理:








SQL> truncate table Metastylesheet;


Table truncated. 




然後可以繼續進行轉換！









SQL> ALTER SESSION SET SQL_TRACE=TRUE;

Session altered.

SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;

Database altered.

SQL> ALTER SESSION SET SQL_TRACE=FALSE;

Session altered.
    
    


在9.2.0中，轉換完成以後，可以通過執行catmet.sql指令碼來重建Metastylesheet表:











SQL> @?/rdbms/admin/catmet.sql


轉換後的資料：







SQL> select name,value$ from props$ where name like '%NLS%';

NAME                           VALUE$
------------------------------ ------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               ZHS16GBK
…..
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              9.2.0.4.0

20 rows selected.

SQL> select * from eygle.test;

NAME
------------------------------
測試
test

2 rows selected.
   


提示:
通過設定sql_trace，我們可以跟蹤很多資料庫的後臺操作，這個工具是DBA常用的“利器”之一。
我們簡單看一下資料庫更改字符集時的後臺處理，我提取了主要的更新部分。
通過以下跟蹤過程，我們看到資料庫在更改字符集的時候，主要更新了12張資料字典表，修改了資料庫的原資料，這也證實了我們以前的說法: 
這個更改字符集的操作在本質上並不轉換任何資料庫字元，只是簡單的更新資料庫中所有跟字符集相關的資訊。







update col$ set charsetid = :1 
where
 charsetform. = :2


update argument$ set charsetid = :1 
where
 charsetform. = :2


update collection$ set charsetid = :1 
where
 charsetform. = :2


update attribute$ set charsetid = :1 
where
 charsetform. = :2


update parameter$ set charsetid = :1 
where
 charsetform. = :2


update result$ set charsetid = :1 
where
 charsetform. = :2


update partcol$ set spare1 = :1 
where
 charsetform. = :2


update subpartcol$ set spare1 = :1 
where
 charsetform. = :2


update props$ set value$ = :1 
where
 name = :2


update "SYS"."KOTAD$" set SYS_NC_ROWINFO$ = :1 
where
 SYS_NC_OID$ = :2

update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6,
  cache=:7,highwater=:8,audit$=:9,flags=:10 
where
 obj#=:1

update kopm$ set metadata = :1, length  = :2 
where
 name='DB_FDO'
    
    


 


在這裡我們順便糾正一個由來以及的錯誤方法.
經常可以在網上看到這樣的更改字符集的方法：









1）用SYS使用者名稱登陸ORACLE。


2）檢視字符集內容


SQL>SELECT * FROM PROPS$;


3）修改字符集


SQL> update props$ set value$='新字符集' where name='NLS_CHARACTERSET'


4) COMMIT; 


 



我們看到很多人在這個問題上遇到了慘痛的教訓，使用這種方式更改字符集，如果你的value$值輸入了不正確的字符集，在8i中那麼你
的資料庫可能會無法啟動，這種情況是非常嚴重的,有時候你必須從備份中進行恢復；如果是在9i中，可以重新啟動資料庫後再修改回正
確的字符集。但是我們仍然不建議使用這種方式進行任何資料庫修改，這是一種極其危險的操作。
實際上當我們更新了字符集，資料庫啟動時會根據資料庫的字符集自動的來修改控制檔案的字符集，如果字符集可以識別，更新控制文
件字符集等於資料庫字符集；如果字符集不可識別，那麼控制檔案字符集更新為US7ASCII.


通過更新props$表的方式修改字符集,在Oracle7之後就不應該被使用.


以下是我的測試結果，但是嚴禁一切不備份的修改研究，即使是對測試庫的。







SQL> update props$ set value$='EYGLE' where name='NLS_CHARACTERSET';

1 row updated.

SQL> commit;

Commit complete.

SQL> select name,value$ from props$ where name like '%NLS%';

NAME                           VALUE$
------------------------------ -----------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               EYGLE
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
….
NLS_NCHAR_CHARACTERSET         ZHS16GBK
NLS_RDBMS_VERSION              8.1.7.1.1

18 rows selected.

重新啟動資料庫，發現alert.log檔案中記錄如下操作:

Mon Nov 03 16:11:35 2003
Updating character set in controlfile to US7ASCII
Completed: ALTER DATABASE OPEN

啟動資料庫後恢復字符集設定:

SQL> update props$ set value$='ZHS16GBK' where name='NLS_CHARACTERSET';

1 row updated.

SQL> commit;

Commit complete.

SQL> select name,value$ from props$ where name like '%NLS%';

NAME                           VALUE$
------------------------------ -----------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               ZHS16GBK
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
………
NLS_COMP                       BINARY
NLS_NCHAR_CHARACTERSET         ZHS16GBK
NLS_RDBMS_VERSION              8.1.7.1.1

18 rows selected.

重新啟動資料庫後，發現控制檔案的字符集被更新:

Mon Nov 03 16:21:41 2003
Updating character set in controlfile to ZHS16GBK
Completed: ALTER DATABASE OPEN
oracle9205,
在做字符集轉換的時候，發現只有兩個clob的型別的欄位：

User  : SYS
Table : METASTYLESHEET

User  : SYS
Table : EXTERNAL_TAB$

在轉換之前，要把兩個表truncate 掉，然後在轉換，
轉換完成後，重新使用rdms/下的sql重建這兩個表，

我看過資料，
sys.metastylesheet，可以使用catmet.sql重新建立，
那麼sys.EXTERNAL_TAB$用那個指令碼建立呢？
我在轉換前，用create table aaa as select *　from sys.external_tab$,做個ｂａｃｋｕｐ，我在轉換前，用create table aaa as select *　from sys.external_tab$,做個ｂａｃｋｕｐ，然後ｔｒｕｎｃａｔｅ這個ｓｙｓ．external_tab$．，轉換完成後再把資料弄回去，這樣就可以了。
然後ｔｒｕｎｃａｔｅ這個ｓｙｓ．external_tab$．，轉換完成後再把資料弄回去，這樣就可以了。

 


理解了字符集調整的內部操作以後，我們可以輕易的指出，以上的方法是不正確的，通過前面 ” ALTER DATABASE CHARACTER SET” 方式更改字
符集時，Oracle至少需要更改12張資料字典表，而這種直接更新props$表的方式只完成了其中十二分之一的工作，潛在的完整性隱患是可想而知的。


所以，更改字符集儘量要使用正常的途徑。



來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/241379/viewspace-694442/，如需轉載，請註明出處，否則將追究法律責任。







相關文章