Changing the Character Set After Database Creation

[erwan]

Hello !
IS it Possible the to change the Character Set After Database Creation.
I have been created my instance with the character set = WE8ISO8859P1 and now i want to use cyrilic character.
I need to change the character set to EE8ISO8859P2.

Do you think is it possible with recreating the instance.

Thanks for your answers.
Erwan

 

[Shantha]

Yes it is possible to do.

ALTER DATABASE [<db_name>] CHARACTER SET <new_character_set>;
ALTER DATABASE [<db_name>] NATIONAL CHARACTER SET <new_NCHAR_character_set>;


The database name is optional. The character set name should be specified without quotes, for example:

ALTER DATABASE CHARACTER SET WE8ISO8859P1;


To change the database character set, perform the following steps. Not all of them are absolutely necessary, but they are highly recommended:

SQL> SHUTDOWN IMMEDIATE; -- or NORMAL
<do a full backup>

SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET <new_character_set_name>;
SQL> SHUTDOWN IMMEDIATE; -- or NORMAL
SQL> STARTUP;


To change the national character set, replace the ALTER DATABASE CHARACTER SET statement with ALTER DATABASE NATIONAL CHARACTER SET. You can issue both commands together if desired.

Attempting to change the database character set to a character set that is not a strict superset can result in data loss and data corruption. To ensure data integrity, whenever migrating to a new character set that is not a strict superset, you must use export/import. It is essential to do a full backup of the database before using the ALTER DATABASE [NATIONAL] CHARACTER SET statement, since the command cannot be rolled back

Hope this helps.
Talent is what you possess;
genius is what possesses you

 

[karluk]

Basically the documentation given by Shantha is saying that you shouldn't alter the database character set unless you are willing to drop and recreate your database with the new character set and then import the data from the old database. Unless you are lucky enough to have a new character set that is a superset of the old, you will mess up everything that is currently in the database. I haven't checked, but I seriously doubt that EE8ISO8859P2 is a superset of WE8ISO8859P1. That means you are reduced to using the export/import method of changing character sets.