oracle数据库字符集的转换

—１—修改数据库里的字符集

—中文字符集转英文字符集
–英文

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;