Modifying character sets in Oracle

Exp encountered ORA-06552 error solution (PLS-553: Character Set Name is not recognized) There is a built data, Oracle 9.2.0.4 for Linux, character set is the default we8iso8859p1, not changed to zhs16gbk. the database does not have any data.

Because the character set is not a superset/subset relationship, you cannot modify the character set through alter database character set.

At that time, the database was not rebuilt, but the prop $ table was directly modified and the nls_char character set was changed to zhs16gbk.
Update props $ set value $ = 'zhs16gbk' where name = 'nls _ characterset ';
After the restart, create the table and insert the data. Everything is normal... The exp data will be generated until today.

Execute exp userid = system owner = username... error!
Will export the specified user...
. Exporting objects and operations in the pre-schema Process
. Exporting username's external function library name
. Export public type Synonyms
EXP-00008: Oracle error 6552
ORA-06552: PL/SQL: compilation unit analysis terminated
ORA-06553: PLS-553: Character Set Name is not recognized
EXP-00000: Export termination failed

The implicit feeling is related to the character set settings at that time. Http://www.eygle.com/special/nls_character_set_03.htm: "to formally modify a character set, Oracle must change at least 12 data dictionary tables. In this way, only 1/12 of the data dictionary tables are updated directly, potential integrity hazards can be imagined."

But how can this problem be solved? Later, an undisclosed internal_use usage was found, which forced the character set to be consistent and solved the problem.

First, check whether the character set is not completely modified.
Select distinct (nls_charset_name (charsetid) characterset,
Decode (type #, 1,
Decode (charsetform, 1, 'varchar2', 2, 'varchar2', 'unkown '),
9,
Decode (charsetform, 1, 'varchar ', 2, 'nchar varying', 'unkown '),
96,
Decode (charsetform, 1, 'Char ', 2, 'nchar', 'unkown '),
112,
Decode (charsetform, 1, 'clob', 2, 'clob', 'unkown ') types_used_in
From SYS. Col $
Where charsetform in (1, 2)
And type # In (1, 9, 96,112 );

If the preceding query does show multiple character set settings, perform the following operations:
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 ';

-- Internal_use is a parameter that is not written in the document and is used to force character set consistency.
Alter database character set internal_use & charset;
Alter database National Character Set internal_use & ncharset;

Shutdown immediate;
Startup;
-- Start the database again
Shutdown immediate;
Startup;

So far, the exp problem has been solved.

Note: modifying the character set only modifies the data dictionary and does not convert the data character set!
 

 

Transfer