ORA 6552, ORA 6553, PLS 553

varchar2가 mix되어서 export시에 에러발생.

full export error

table export error

공통적으로 ORA 6552, ORA 6553  에러가 발생한다.
메타링크를 참조하여 해결책은 다음과 같다.

1. 캐릭터셋 정보 확인

SELECT VALUE
 FROM NLS_DATABASE_PARAMETERS
 WHERE PARAMETER in ('NLS_NCHAR_CHARACTERSET','NLS_CHARACTERSET') ;

VALUE
--------------------------------------------------------------------------------
KO16KSC5601
UTF8

 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);

CHARACTERSET TYPES_USED_IN
---------------------------------------- -------------
WE8ISO8859P1 VARCHAR2      
UTF8 NVARCHAR2
UTF8 NCLOB
KO16KSC5601 VARCHAR2        
WE8ISO8859P1 CHAR
WE8ISO8859P1 CLOB
UTF8 NCHAR

위와같이 VARCHAR2가 중복되어서 지정된것을 확인할수 있다.

 

Solution.

1. show parameter parallel_server;    ==>false 또는 init.ora not set

2.sysdba 로  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;

3. parallel_server parameter 복원

note 286964.1
PLS-553 when calling or compiling pl/sql objects

(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.