Jump to Navigation

168 - Change Oracle DB Chracter Set

admin - Mon, 05/09/2011 - 11:02

If you want to change from WE8MSWIN1252 to AL32UTF8. Check the NLS parameters first.

SQL> SELECT PARAMETER, VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

The syntax of the ALTER DATABASE CHARACTER SET statement is as follows:

SQL> ALTER DATABASE [db_name] CHARACTER SET new_character_set;

(db_name is an optional)

For Example :

SQL> ALTER DATABASE CHARACTER SET AL32UTF8;

 

To change the database character set, perform the following steps:

1. Shut down the database, using either a SHUTDOWN IMMEDIATE or a SHUTDOWN NORMAL statement.

2. Do a full backup of the database because the ALTER DATABASE CHARACTER SET statement cannot be rolled back.

3. Startup Oracle database

SQL> startup mount;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET AL32UTF8;

If you get the error ORA-12712, please update the following table.

"ORA-12712: new character set must be a superset of old character set"

SQL> update sys.props$ set VALUE$='AL32UTF8' where NAME='NLS_CHARACTERSET';
SQL> commit;

If you get the error ORA-12721, please login as DBA user.

"ORA-12721: operation cannot execute when other sessions are active"

4. shutdown immediate;  or shutdown normal;

5. startup oracle database

SQL> startup mount;
SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET AL32UTF8;

SQL> shutdown immediate;
SQL> startup;

SQL> SELECT PARAMETER, VALUE FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER IN ('NLS_CHARACTERSET', 'NLS_NCHAR_CHARACTERSET');

 

------------------------------------------------------------------------------
AL16UTF16 Unicode 3.1 UTF-16 Universal Charactor Set : MB, EURO, FIXED
AL32UTF8  Unicode 3.1 UTF-8 Universal Charactor Set : MB, ASCII, EURO
UTF8      Unicode 3.0 UTF-8 Universal Charactor Set CESU : MB, ASCII, EURO

MB : Multibyte Encoding
FIXED : Fixed Mutibyte Encoding
EURO : Support Euro Code
Oracle:


Main menu 2

Story | by Dr. Radut