Oracle 12c中文亂碼，修改字符集的方法

在Windows 7 64位上安裝Oracle 12c沒有設定字符集，采用的是操作系統默認字符集：WE8MSWIN1252，將字符集修改為：ZHS16GBK。由于過程不可逆，首先需要備份數據庫。 1.數據庫全備

2.查詢當前字符集 SQL> select * from nls_database_parameters where parameter=’NLS_CHARACTERSET’; PARAMETER VALUE

NLS_CHARACTERSET WE8ISO8859P1 3.關閉數據庫

SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. 4.啟動數據庫到mount狀態

復制代碼 SQL> startup mount ORACLE instance started. Total System Global Area 205520896 bytes Fixed Size 1266608 bytes Variable Size 100666448 bytes Database Buffers 100663296 bytes Redo Buffers 2924544 bytes Database mounted. 復制代碼 5.限制session

SQL> alter system enable restricted session; System altered. 6.查詢相關參數并修改 復制代碼 SQL> show parameter job_queue_PRocesses; NAME TYPE VALUE

job_queue_processes integer 10

SQL> show parameter aq_tm_processes; NAME TYPE VALUE

aq_tm_processes integer 0

SQL> alter system set job_queue_processes=0; System altered. 復制代碼 7.打開數據庫 SQL> alter database open; Database altered. 8.修改字符集 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 出現錯誤提示，新字符集必須是老字符集的超集，也就原來字符集是新字符集的子集，可以再Oracle官方文檔上查詢字符集包含關系。下面使用Oracle內部命令internal_use，跳過超集檢查，生產環境不建議使用此方法。 SQL> alter database character set internal_use ZHS16GBK; Database altered. 9.查詢當前字符集 SQL> select * from nls_database_parameters where parameter=’NLS_CHARACTERSET’; PARAMETER VALUE

NLS_CHARACTERSET ZHS16GBK 10.關閉數據庫 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. 11.啟動數據庫到mount狀態 復制代碼 SQL> startup mount ORACLE instance started. Total System Global Area 205520896 bytes Fixed Size 1266608 bytes Variable Size 100666448 bytes Database Buffers 100663296 bytes Redo Buffers 2924544 bytes Database mounted. 復制代碼 12.將相關參數改回原來值 SQL> alter system set job_queue_processes=10; System altered. 13.打開數據庫 SQL> alter database open; Database altered.

在windows 7 64位上安裝Oracle 12c沒有設定字符集，采用的是操作系統默認字符集：WE8MSWIN1252，將字符集修改為：ZHS16GBK。由于過程不可逆，首先需要備份數據庫。 1.數據庫全備

2.查詢當前字符集 SQL> select * from nls_database_parameters where parameter=’NLS_CHARACTERSET’; PARAMETER VALUE

NLS_CHARACTERSET WE8ISO8859P1 3.關閉數據庫

SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. 4.啟動數據庫到mount狀態

復制代碼 SQL> startup mount ORACLE instance started. Total System Global Area 205520896 bytes Fixed Size 1266608 bytes Variable Size 100666448 bytes Database Buffers 100663296 bytes Redo Buffers 2924544 bytes Database mounted. 復制代碼 5.限制session

SQL> alter system enable restricted session; System altered. 6.查詢相關參數并修改 復制代碼 SQL> show parameter job_queue_processes; NAME TYPE VALUE

job_queue_processes integer 10

SQL> show parameter aq_tm_processes; NAME TYPE VALUE

aq_tm_processes integer 0

SQL> alter system set job_queue_processes=0; System altered. 復制代碼 7.打開數據庫 SQL> alter database open; Database altered. 8.修改字符集 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 出現錯誤提示，新字符集必須是老字符集的超集，也就原來字符集是新字符集的子集，可以再Oracle官方文檔上查詢字符集包含關系。下面使用Oracle內部命令internal_use，跳過超集檢查，生產環境不建議使用此方法。 SQL> alter database character set internal_use ZHS16GBK; Database altered. 9.查詢當前字符集 SQL> select * from nls_database_parameters where parameter=’NLS_CHARACTERSET’; PARAMETER VALUE

NLS_CHARACTERSET ZHS16GBK 10.關閉數據庫 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. 11.啟動數據庫到mount狀態 復制代碼 SQL> startup mount ORACLE instance started. Total System Global Area 205520896 bytes Fixed Size 1266608 bytes Variable Size 100666448 bytes Database Buffers 100663296 bytes Redo Buffers 2924544 bytes Database mounted. 復制代碼 12.將相關參數改回原來值 SQL> alter system set job_queue_processes=10; System altered. 13.打開數據庫 SQL> alter database open; Database altered.