修改字符集CHARACTERSET的方法

tyyms
3 ℃
2020-01-07

整理文档很辛苦，赏杯茶钱您下走！

还剩 ... 页未读，继续阅读 >>

免费阅读已结束，点击下载阅读编辑剩下 ... 页

阅读已结束，您可以下载文档离线阅读编辑

资源描述

一、在Redhat上安装Oracle11g没有设定字符集，采用的是操作系统默认字符集，由于过程不可逆，首先需要备份数据库。1.数据库全备2.查询当前字符集SQLselect*fromnls_database_parameterswhereparameter='NLS_CHARACTERSET';PARAMETERVALUE--------------------------------------------------------------------------------NLS_CHARACTERSETWE8ISO8859P13.关闭数据库SQLshutdownimmediateDatabaseclosed.Databasedismounted.ORACLEinstanceshutdown.4.启动数据库到mount状态SQLstartupmountORACLEinstancestarted.TotalSystemGlobalArea205520896bytesFixedSize1266608bytesVariableSize100666448bytesDatabaseBuffers100663296bytesRedoBuffers2924544bytesDatabasemounted.5.限制sessionSQLaltersystemenablerestrictedsession;Systemaltered.6.查询相关参数并修改SQLshowparameterjob_queue_processes;NAMETYPEVALUE-----------------------------------------------------------------------------job_queue_processesinteger10SQLshowparameteraq_tm_processes;NAMETYPEVALUE-----------------------------------------------------------------------------aq_tm_processesinteger0SQLaltersystemsetjob_queue_processes=0;Systemaltered.7.打开数据库SQLalterdatabaseopen;Databasealtered.8.修改字符集SQLalterdatabasecharactersetZHS16GBK;alterdatabasecharactersetZHS16GBK*ERRORatline1:ORA-12712:newcharactersetmustbeasupersetofoldcharacterset出现错误提示，新字符集必须是老字符集的超集，也就原来字符集是新字符集的子集，可以再Oracle官方文档上查询字符集包含关系。下面使用Oracle内部命令internal_use，跳过超集检查，生产环境不建议使用此方法。SQLalterdatabasecharactersetinternal_useZHS16GBK;Databasealtered.9.查询当前字符集SQLselect*fromnls_database_parameterswhereparameter='NLS_CHARACTERSET';PARAMETERVALUE--------------------------------------------------------------------------------NLS_CHARACTERSETZHS16GBK10.关闭数据库SQLshutdownimmediateDatabaseclosed.Databasedismounted.ORACLEinstanceshutdown.11.启动数据库到mount状态SQLstartupmountORACLEinstancestarted.TotalSystemGlobalArea205520896bytesFixedSize1266608bytesVariableSize100666448bytesDatabaseBuffers100663296bytesRedoBuffers2924544bytesDatabasemounted.12.将相关参数改回原来值SQLaltersystemsetjob_queue_processes=10;Systemaltered.13.打开数据库SQLalterdatabaseopen;Databasealtered.问题依旧在我的OrclSQLselect*fromnls_database_parameterswhereparameter='NLS_CHARACTERSET';PARAMETERVALUE--------------------------------------------------------------------------------NLS_CHARACTERSETZHS16GBK