Changing the character set on an existing Oracle based RequisitePro project database

Resolving The Problem

The information detailed in this solution can be used as an alternative to the suggested means of changing a character set (export/import into a new database) outlined in solution 1151151.

This functionality is only available with Oracle8i databases and higher.

Altering the character set of an existing database is a signification system change and should not be attempted without the agreement and assistance of the Database Administrator.

Changing the character set on an existing database could cause applications that run on the
database to lose functionality.

The potential impact of changing the character set of a database should be carefully reviewed before implementation.

The following conditions must exist to change the character set on a existing Oracle database.

1. You must have the SYSDBA system privilege.
2. The database must be started in restricted mode.
3. The current character set must be a strict subset of the character set to
   which you change.

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

It is highly recommended to do a full backup of the database before using the ALTER DATABASE CHARACTER SET statement, since the command cannot be rolled back.

Here are the steps to change the character set of an existing database.

SQL> SHUTDOWN IMMEDIATE;  -- or NORMAL  <Have the DBA perform a full
backup>
SQL> CONNECT <username>/<password> AS SYSDBA
SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICTED 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;


Use the following query to confirm that the character set of the database has
been changed.


SELECT Name, Value$ FROM sys.props$ WHERE Name='NLS_CHARACTERSET';