Subject:  V8: Changing the database or national character set 
Type:  BULLETIN 
Creation Date:  23-OCT-1998 
 

PURPOSE
 
  To explain how to change the database or national character set of
  an existing Oracle8 database without having to recreate the database.

SCOPE & APPLICATION
 
  The method described here is documented in the Oracle 8.1.x documentation.
  It is not documented but it can be used in version 8.0.x.

  Before using this method it is essential to do a full backup of the 
  database.

  This method does not work in Oracle7.

  Note that changing the database or the national character set as described 
  in this document does not change the actual character codes, it only changes 
  the character set declaration. If you want to convert the contents of the 
  database from one character set to another you must use the Oracle Export 
  and Import utilities. This is needed, for example, if the source character 
  set is not a subset of the target character set.

RELATED DOCUMENTS
 
  [NOTE:13856.1] V7: CHANGING DATABASE CHARACTER SET
  [NOTE:62107.1] The National Character Set in Oracle8

  Oracle8i National Language Support Guide



CHANGING THE DATABASE OR NATIONAL CHARACTER SET
================================================

In Oracle7 it is possible to update the "SYS.PROPS$" table to change the 
database character set.  The method is unsupported but it is externally 
published (see [NOTE:13856.1]).  It is used by Oracle Installer to install 
seed databases.

This method is not legal in Oracle8 because character set information is 
stored in many other places in the Data Dictionary besides SYS.PROPS$, for 
example with each table column, PL/SQL argument, etc.
 
In Oracle8 there is another way of changing the database or national character 
set.  The method uses two commands, which are documented in the Oracle8i 
National Language Support Guide:
 
   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 these steps are absolutely necessary, but they are highly recommended:
 
    SVRMGR> SHUTDOWN IMMEDIATE;   -- or NORMAL

        <do a full database backup>

    SVRMGR> STARTUP MOUNT; 
    SVRMGR> ALTER SYSTEM ENABLE RESTRICTED SESSION; 
    SVRMGR> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; 
    SVRMGR> ALTER SYSTEM SET AQ_TM_PROCESSES=0; 
    SVRMGR> ALTER DATABASE OPEN; 
    SVRMGR> ALTER DATABASE CHARACTER SET <new_character_set>; 
    SVRMGR> SHUTDOWN IMMEDIATE;   -- OR NORMAL
    SVRMGR> STARTUP; 
 
To change the national character set replace the ALTER DATABASE CHARACTER
SET command with ALTER DATABASE NATIONAL CHARACTER SET.  You can issue both
commands together if you wish.

The ALTER DATABASE [NATIONAL] CHARACTER SET command will return:

    ORA-01679: database must be mounted EXCLUSIVE and not open to activate

    - if you do not enable restricted session
    - if you startup the instance in PARALLEL/SHARED mode
    - if you do not set the number of queue processes to 0
    - if you do not set the number of AQ time manager processes to 0
    - if anybody is logged in apart from you.

This error message is misleading.  The command requires the database to be
open but only one session, the one executing the command, is allowed.

The above method will only work if the old character set is US7ASCII (or if 
you change the character set to itself).  If the old character set is neither 
US7ASCII nor equal to the new character set, the ALTER DATABASE [NATIONAL] 
CHARACTER SET command will return:
    
    - in Oracle 8.1.5 and above:

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

    - in Oracle 8.0.5 and 8.0.6:

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

    - in Oracle 8.0.3 and 8.0.4:

      ORA-24329: invalid character set identifier

 
the ALTER DATABASE [NATIONAL] CHARACTER SET command:

 ALTER DATABASE [<db_name>]
    [NATIONAL] CHARACTER SET password <new_character_set>
 
 If included in the statement this option will switch the character set
 verification off allowing any database character set to be changed.

 If justified by customer's situation, Development may be able to backport
 this option to Oracle 8.0.  The base bug for the backport
 is [BUG:508017].