Emre Baransel - Oracle Blog

-----------------------

This article describes how one can change the character set of a database.It should be done with extreme caution having noted the following limitations.The database character set should only be changed if the characters of the code page of the originating database correspond to the same characters of the target database, ie. if the database was created with the characterset US7ASCII and it is to be updated to WE8ISO8859P1. Since these have the same encoding scheme for the first 127 bits, changing the character set from US7ASCII to WE8ISO8859P1 will display all characters up to 127 bits as the same character before and after. In addition, in this particular example, if any characters have been entered with the 8th bit set, then updating the database characterset to 8 bit will allow that 8th bit to be displayed. You must not change the characterset from one encoding scheme to another encoding scheme where the code pages do not correspond. This will completely scramble your database. In addition, if case*designer diagrams are stored in the database, this method must not be used.

Before attempting to run any of the scripts below, you must take a full cold backup of your database. In addition, the procedure must be thoroughly tested before attempting this on a production instance.

Here's a SQL*PLUS script that allows a database's character set to be changed to a different encoding scheme without having to rebuild the database.

set echo off

set verify off

The data dictionary table that records the database

character set is sys.props$

SQL> describe sys.props$

Name Null? Type

------------------------------- -------- ----

NAME NOT NULL VARCHAR2(30)

VALUE$ VARCHAR2(2000)

COMMENT$ VARCHAR2(2000)

For example:

SQL> column c1 format a30

SQL> select name c1, value$ c1 from sys.props$;

C1 C1

------------------------------ ------------------------------

DICT.BASE 2

NLS_LANGUAGE AMERICAN

NLS_TERRITORY AMERICA

NLS_CURRENCY $

NLS_ISO_CURRENCY AMERICA

NLS_NUMERIC_CHARACTERS .,

NLS_DATE_FORMAT DD-MON-YY

NLS_DATE_LANGUAGE AMERICAN

NLS_CHARACTERSET WE8DEC

NLS_SORT BINARY

GLOBAL_DB_NAME NLSV7.WORLD

NLS_CHARACTERSET can be changed by updating its value, for example:

update sys.props$

set value$ = 'WE8ISO8859P1'

Where name = 'NLS_CHARACTERSET';

The database has to be shutdown and restarted before the change becomes effective.
It is very important to specify the character set name correctly.

IMPORTANT NOTE

=============

If NLS_CHARACTERSET is updated to an invalid value, it will not then be possible to restart the database once it has been shutdown. To recover, it will be necessary to re-create the database, since it cannot be restarted to correct the invalid NLS_CHARACTERSET entry.

The character set name should be in uppercase.

The new value is not effective until the database has been shutdown and restarted. A suggested procedure is as follows, and can be done by running this script from SQL*Plus when logged into the SYSTEM account.

USAGE : SQL> start ch_db.sql

where is the desired database character set

Prompt First check that the character set name is valid.

set echo on

select convert('a','&1','us7ascii') from dual;

set echo off

prompt If this select statement returns error ORA-01482, then the prompt specified character set name is not valid for this installation.
prompt Abort the procedure now with Control-c
prompt To continue, press return
accept ans CHAR
Prompt Check the current value of database character set.

column c1 format a30

select name c1, value$ c1 from sys.props$
where name = 'NLS_CHARACTERSET';

prompt To continue, press return
Prompt Update to new character set

update sys.props$
set value$ = upper('&1')
where name = 'NLS_CHARACTERSET';

set echo off
prompt To continue, press return
accept ans CHAR
Prompt Check the new value of database character set

select name c1, value$ c1 from sys.props$
where name = 'NLS_CHARACTERSET';

Prompt If the value is updated as required, press return to continue and
Prompt then manually type COMMIT; to commit the change. Then shutdown and
Prompt restart the database.
Prompt
Prompt If the value is not updated as required, press return to continue and
Prompt than manually type ROLLBACK; to prevent the change.
prompt To continue, press return
accept ans CHAR