---１---修改資料庫裡的字符集

---中文字符集轉英文字符集

--英文

update sys.props$ set value$='US7ASCII' where name='NLS_CHARACTERSET';

commit;

update sys.props$ set value$='US7ASCII' where name='NLS_NCHAR_CHARACTERSET';

commit;

---２---修改強制同步資料庫裡的字符集

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER SYSTEM ENABLE RESTRICTED SESSION;

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

ALTER SYSTEM SET AQ_TM_PROCESSES=0;

ALTER DATABASE OPEN;

---給CHARSET變數賦值

COL VALUE NEW_VALUE CHARSET

SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';

COL VALUE NEW_VALUE NCHARSET

SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';

--INTERNAL_USE是沒有寫在文件中的引數，用以強制完成字符集一致化

ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET;

--ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET;

-- 再次啟動資料庫一遍

SHUTDOWN IMMEDIATE;

STARTUP;

set nls_lang=AMERICAN_AMERICA.US7ASCII;

---英文字符集轉中文字符集

update sys.props$ set value$='ZHS16GBK' where name='NLS_CHARACTERSET';

commit;

update sys.props$ set value$='ZHS16GBK' where name='NLS_NCHAR_CHARACTERSET';

commit;

---２---修改強制同步資料庫裡的字符集

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

ALTER SYSTEM ENABLE RESTRICTED SESSION;

ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;

ALTER SYSTEM SET AQ_TM_PROCESSES=0;

ALTER DATABASE OPEN;

---給CHARSET變數賦值

COL VALUE NEW_VALUE CHARSET

SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_CHARACTERSET';

COL VALUE NEW_VALUE NCHARSET

SELECT VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER='NLS_NCHAR_CHARACTERSET';

--INTERNAL_USE是沒有寫在文件中的引數，用以強制完成字符集一致化

ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET;

--ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET;

-- 再次啟動資料庫一遍

SHUTDOWN IMMEDIATE;

STARTUP;

set nls_lang=SIMPLIFIED CHINESE_CHINA.ZHS16GBK;

oracle資料庫字符集的轉換

相關文章