由于字符集修改不完整导致导出数据报:pls-553错误

郑全

执行exp full=y,就报这个字符集不认识的错误

. exporting PUBLIC type synonyms
EXP-00008: ORACLE error 6552 encountered
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-553: character set name is not recognized
EXP-00000: Export terminated unsuccessfully

这个问题主要是字符集修改不正确所导致.
下面验证字符集是否修改正确:

SELECT DISTINCT (NLS_CHARSET_NAME(CHARSETID)) CHARACTERSET,
DECODE(TYPE#, 1,
DECODE(CHARSETFORM, 1, 'VARCHAR2', 2, 'NVARCHAR2', 'UNKOWN'),
9,
DECODE(CHARSETFORM, 1, 'VARCHAR', 2, 'NCHAR VARYING', 'UNKOWN'),
96,
DECODE(CHARSETFORM, 1, 'CHAR', 2, 'NCHAR', 'UNKOWN'),
112,
DECODE(CHARSETFORM, 1, 'CLOB', 2, 'NCLOB', 'UNKOWN')) TYPES_USED_IN
FROM SYS.COL$
WHERE CHARSETFORM IN (1, 2)
AND TYPE# IN (1, 9, 96, 112);

执行以上语句,正确的应该是只有一个字符集,如果有多个,就有问题,我这里,正好就有3个,
ZHS16CGB231280
ZHS16GBK
正常的,只有一个zhs16gbk

处理办法:

STARTUP MOUNT;
ALTER SYSTEM ENABLE RESTRICTED SESSION;
ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
ALTER SYSTEM SET AQ_TM_PROCESSES=0;
ALTER DATABASE OPEN;

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;

至此,再去进行数据导出,不再报错.

郑全

环境: solaris 5.9
db: oracle 9.2.0.1