ORA-06553: PLS-553: character set name is not recognized, while starting Content Store

今天用PLSQL的时候通过EXCEL导入数据，发现中文均显示为?

确认是数据库字符集没有设置正常：对比正常的数据库发现NLS_CHARACTERSET为ZHS16GBK

SQL> select name,value$ from props$ where name like 'NLS%';NAME                           VALUE$------------------------------ ----------------------------------------NLS_LANGUAGE                   AMERICANNLS_TERRITORY                  AMERICANLS_CURRENCY                   $NLS_ISO_CURRENCY               AMERICANLS_NUMERIC_CHARACTERS         .,NLS_CHARACTERSET               ZHS16GBKNLS_CALENDAR                   GREGORIANNLS_DATE_FORMAT                DD-MON-RRNLS_DATE_LANGUAGE              AMERICANNLS_SORT                       BINARYNLS_TIME_FORMAT                HH.MI.SSXFF AMNAME                           VALUE$------------------------------ ----------------------------------------NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AMNLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZRNLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZRNLS_DUAL_CURRENCY              $NLS_COMP                       BINARYNLS_LENGTH_SEMANTICS           BYTENLS_NCHAR_CONV_EXCP            FALSENLS_NCHAR_CHARACTERSET         AL16UTF16NLS_RDBMS_VERSION              10.2.0.4.0

网上提供的方式是直接update这个列的值，但update以后

SQL> update props$ set value$='AL32UTF8' where name='NLS_CHARACTERSET';

查看一下当前数据库的字符集信息，虽然视图显示修改成功了。但

SQL> col value for a40SQL> Select * from NLS_DATABASE_PARAMETERS;PARAMETER                      VALUE------------------------------ ----------------------------------------NLS_LANGUAGE                   AMERICANNLS_TERRITORY                  AMERICANLS_CURRENCY                   $NLS_ISO_CURRENCY               AMERICANLS_NUMERIC_CHARACTERS         .,NLS_CHARACTERSET               AL32UTF8NLS_CALENDAR                   GREGORIANNLS_DATE_FORMAT                DD-MON-RRNLS_DATE_LANGUAGE              AMERICANNLS_SORT                       BINARYNLS_TIME_FORMAT                HH.MI.SSXFF AMPARAMETER                      VALUE------------------------------ ----------------------------------------NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AMNLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZRNLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZRNLS_DUAL_CURRENCY              $NLS_COMP                       BINARYNLS_LENGTH_SEMANTICS           BYTENLS_NCHAR_CONV_EXCP            FALSENLS_NCHAR_CHARACTERSET         AL16UTF16NLS_RDBMS_VERSION              11.2.0.1.0

进行DDL等操作的时候会出现ORA-06553: PLS-553 的错误提示：

CM-CFG-5029 Content Manager is unable to determine whether the content store is initialized.ORA-06552: PL/SQL: Compilation unit analysis terminatedORA-06553: PLS-553: character set name is not recognized

可以判断是字符集冲突导致的，查看

ALTER DATABASE CHARACTER SET ZHS16GBK;ALTER DATABASE CHARACTER SET INTERNAL_USE ZHS16GBK;

修改的时候还遇到一个错误提示：

ERROR at line 1:ORA-12719: operation requires database is in RESTRICTED mode[oracle@021Y-SH-BKAP ~]$ oerr ora 1271912719, 00000, "operation requires database is in RESTRICTED mode"// *Cause: This command can only be run when the database is in RESTRICTED mode// *Action: Ensure that the system is in RESTRICTED mode

解决方式：

SHUTDOWN IMMEDIATE;  STARTUP MOUNT EXCLUSIVE;  ALTER SYSTEM ENABLE RESTRICTED SESSION

确认修改成功后，重启数据库。注意如果是生产环境重启前也要关闭工作计划。

show parameter job_queue_processes;show parameter aq_tm_processes;alter database set job_queue_processes=0;