原帖由 Kamus 于 2008-8-25 22:57 发表
参看这篇文章
http://www.dbform.com/html/2007/309.html
前端时间我也碰到这问题了，最后参考
主题:PLS-553 when calling or compiling pl/sql objects
文档 ID:注释:286964.1 类型:BULLETIN
上次修订日期:04-AUG-2008 状态:PUBLISHED
Problem description
===================
You receive the following error when (re)compiling or calling a piece of pl/sql:
ORA-06550: line , column : ....
or
ORA-06552: PL/SQL: Compilation unit analysis terminated
followed by
ORA-06553: PLS-553: character set name is not recognized
Causes
======
This error may be caused by a "mixup" in the character set of the database.
Verification of the cause
=========================
There are a some issues that can cause this mixup "temporarily", for example
when the database is started through RMAN. This note will not adress those
issues. These issues are described in
Note 179542.1 PLS-00553 When Running Catproc.sql
Note 137377.1 RMAN-6136, PLS-553 when Restoring or Duplicating a Database
Note 164061.1 Manual Upgrading Database From 8.1.6 to 8.1.7 Failes With ORA-06550 and PLS-00553
Note 178719.1 Oracle HTTP Server Displays Error "PLS-00553" After DB Restarted
Note 238946.1 Random Connection Failure when Reconnecting ORA-06550 & PLS-00553
Bug 2990517 PLS-533 when starting the database through RMAN
In general these "temporary" issues can be worked around by doing a shutdown and
startup of the database from a sqlplus session (which is what all the solutions
of these notes basicaly all make you do).
If a restart from sqlplus does not 'fix' the issue for you then please continue
following this note.
You can check if there is indeed a "real" character set mixup by running the
following select statement (as a user with access to the SYS schema):
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);
The typical output of this query in a "healty" system should be 6 or less rows.
For example it should look something like this:
CHARACTERSET
TYPES_USED_IN
-----------------------------------------------------
AL16UTF16
NCHAR
AL16UTF16
NVARCHAR2
AL16UTF16
NCLOB
AL32UTF8
CHAR
AL32UTF8
VARCHAR2
AL32UTF8
CLOB
There should be only 1 distinct character set returned for all N-types
(AL16UTF16 in the above output), and likewise there should be only 1 distinct
character set returned for all normal types (AL32UTF8 in the above output).
For databases that were upgraded from older releases not all 6 rows may show up,
which is normal. As long as all N-types have the same character set, and all
"normal" types have the same character set, then the setup is correct.
A typical incorrect setup would look like this:
CHARACTERSET
TYPES_USED_IN
-----------------------------------------------------
AL16UTF16
NCHAR
AL16UTF16
NVARCHAR2
AL16UTF16
NCLOB
US7ASCII
CHAR
US7ASCII
VARCHAR2
WE8DEC
VARCHAR2
US7ASCII
CLOB
(obviously the actual character sets can differ)
Here you can see that there are 2 different character sets returned for VARCHAR2
data, which indicates a mixup in the database character set setup, which is the
cause for this PLS-553 error.
Correcting the problem
======================
This problem was likely caused when the database character set was changed
incorrectly in the past, and some locations where the character set is stored
were updated and others were not.
In order to correct this problem we can simply run a script that correctly
changes the character set in all the locations to the current database character
set:
a) Make sure the parallel_server parameter in INIT.ORA is set to false or it is not set at all.
SQL>show parameter parallel_server
b) Run the next script in SQLPLUS connected "as sysdba"
Please make sure you have a valid backup to fall back on before you run this script!
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;
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';
ALTER DATABASE CHARACTER SET INTERNAL_USE &CHARSET;
ALTER DATABASE NATIONAL CHARACTER SET INTERNAL_USE &NCHARSET;
SHUTDOWN IMMEDIATE;
STARTUP;
-- yes, 2 times startup/shutdown . This is not a typo
SHUTDOWN IMMEDIATE;
STARTUP;
c) Restore the parallel_server parameter in INIT.ORA, if necessary.
This script doesn't change anything for the data that is already stored, but it
re-enforces database character set to be known in all places where it should be
stored.
After running this you can run the query under "Verification of the cause" once
more, and now there should just 2 sets of character sets, 1 for N-types and 1
for normal types. When that is indeed the case the PLS error will be solved.
Further actions if you had more then 6 entry's:
===============================================
It would be useful to do a healthcheck on this database because it has been
running with this incorrectly converted character set. The following note
describes a "character set health check" for the database:
Note 225938.1 Database Character Set Healthcheck
Further actions if the problem is not solved:
=============================================
If you still have PLS-553 error's then please double check your ORA_NLSxx settings.
Note 77442.1 ORA_NLS (ORA_NLS32, ORA_NLS33, ORA_NLS10) Environment Variables explained.
Please be aware that on Unix you need to restart a process to pickup any change,
it's impossible to say with what setting it was started.
So you will need to restart the listener and database, after double checking the
environment to be sure of the used values.
If you have a random occurence of PLS-553 or errors like
psdgbt: bind csid () does not match session csid () the first of
all apply the fix for Bug 6085625 , note that the fix of this causes Bug 6452485
A merge for 10.2.0.3 (containing both fixes) can be found here:
http://updates.oracle.com/download/6629708.html
This is also fixed in 10.2.0.4 (or higher) and 11.1.0.7 (or higher).
Further reading
===============
Note 257736.1 Changing the Database Character Set - an extended overview
Note 124721.1 Migrating an Applications Installation to a New Character Set
For further NLS / Globalization information you may start here:
Note 267942.1 - Globalization Technology (NLS) Knowledge Browser Product Page
Note 60134.1Globalization (NLS) - Frequently Asked Questions |
