oracle9204更改字符集步驟方法（WE8ISO8859P1－>ZHS16GBK）:
由于我們經常做不同數據庫的導出導入時遇到的服務器的字符集不同，導致我們原有的無法正常導入工作，
這時需要我們做數據庫字符集的更改工作，
字符集是在安裝數據庫時選定的，一旦數據庫創建之后，數據庫的字符集是不能改變的，如果要改變數據庫的字符集， 只有重新創建數據庫。
但通常也可以在特定的條件下通過一些其他途徑來完成字符集的轉換（這里強烈建議創建數據庫時就要考慮好字符集）
單字節=>單字節
多字節=>多字節
且是子集到超集時，可以用alter database CHARACTER set 新的字符集;
通常對于單字節向多字節轉換和非子集到超集轉換時采用exp/imp+internal_use的方法
這里只介紹WE8ISO8859P1－>ZHS16GBK的步驟方法:
檢查當前的字符集為WE8ISO8859P1：
SQL> set wrap off
SQL> select * from v$nls_parameters;
PARAMETER VALUE
---------------------------------------------------------------- ---------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET WE8ISO8859P1
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
PARAMETER VALUE
---------------------------------------------------------------- ---------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET UTF8
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
19 rows selected.
SQL>
將數據庫啟動到RESTRICTED模式下做字符集更改：
SQL> conn /as sysdba
Connected.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
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> ALTER DATABASE CHARACTER SET ZHS16GBK;
ALTER DATABASE CHARACTER SET ZHS16GBK
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set
提示我們的字符集：新字符集必須為舊字符集的超集，這時我們可以跳過超集的檢查做更改：
SQL> ALTER DATABASE character set INTERNAL_USE ZHS16GBK;
Database altered.
SQL> select * from v$nls_parameters;
PARAMETER VALUE
---------------------------------------------------------------- ---------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET ZHS16GBK
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
PARAMETER VALUE
---------------------------------------------------------------- ---------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET UTF8
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
19 rows selected.
重啟檢查是否更改完成：
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 236000356 bytes
Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL> select * from v$nls_parameters;
PARAMETER VALUE
---------------------------------------------------------------- ---------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_CHARACTERSET ZHS16GBK
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
PARAMETER VALUE
---------------------------------------------------------------- ---------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI
NLS_DUAL_CURRENCY $
NLS_NCHAR_CHARACTERSET UTF8
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
19 rows selected.
SQL>
Trackback: http://tb.blog.csdn.net/TrackBack.aspx?PostId=1465644
---------------------------------------------------------------------------------------------------------------------------------
說人之短，乃護己之短。夸己之長，乃忌人之長。皆由存心不厚，識量太狹耳。能去此弊，可以進德，可以遠怨。
http://www.tkk7.com/szhswl
------------------------------------------------------------------------------------------------------ ----------------- ---------
posted on 2007-12-25 19:04
宋針還 閱讀(270)
評論(0) 編輯 收藏 所屬分類:
ORACLE