ORACLE NLS_CHARACTERSET Character Set changes

After the ORACLE NLS_CHARACTERSET Character Set Change database is created, if you need to modify the character set, you usually need to recreate the database and convert it by importing and exporting. You can also change alter database character set in the following ways. Note: exercise caution when modifying database character sets. You must back up the DATABASE before modification. Because this operation cannot be rolled back, data may be lost or damaged. Www.2cto.com is the simplest way to convert character sets, but it is not always valid. This command was introduced to Oracle at Oracle8. In essence, it does not convert any database characters, but simply updates all Character Set-related information in the database. This means that you can only use this conversion method when the new character set is a strict superset of the old character set. Superset means that each character in the current character set can be expressed in the new character set, and the same code points are used. For example, many character sets are strict supersets of US7ASCII. If not superset, the following ERROR is returned: SQL> ALTER DATABASE CHARACTER SET ZHS16CGB231280; ALTER DATABASE CHARACTER SET ZHS16CGB231280 * ERROR at line 1: ORA-12712: new character set must be a superset of old character set www.2cto.com let's look at a test (the following test is performed in Oracle9.2.0, Oracle9i has a big change in coding compared with Oracle8i, in Oracle8i, test results may be slightly different): SQL> select name, value $ from props $ where name like '% NLS %'; NAME VALUE $ ---------------------------------------- ------------------ NLS_LANGUAGE AMERICANNLS_TERRITORY AMERICANLS_CURRENCY $ NLS_ISO_CURRENCY variable., NLS_CHARACTERSET US7ASCIINLS_CALENDAR GREGORIANNLS_DATE_FORMAT DD-MON-RRNLS_DATE_LANGUAGE AMERICAN ................... NLS_NCHAR_CHARACTERSET AL16UTF16NLS_RDBMS_VERSION 9.2.0.4.020 rows selected. SQL> select name, dump (name) from eygle. test; name dump (NAME) ---------------------------------------------------- Test Typ = 1 Len = 4: 178,226,202,212 test Typ = 1 Len = 4: 116,101,115,116 2 rows selected. converts character sets. The database should be in RESTRICTED mode. www.2cto.com c: \> sqlplus "/as sysdba" SQL * Plus: Release 9.2.0.4.0-Production on Sat Nov 1 10:52:30 2003 Copyright (c) 1982,200 2, Oracle Corporation. all rights reserved. connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0-ProductionWith the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining optionsJServer Release 9.2.0.4.0-ProductionSQL> shutdown immediateDatabase closed. database dismounted. ORACLE instance shut down. SQL> STARTUP MOUNT; ORACLE instance started. total System Global Area 76619308 bytesFixed Size 454188 bytesVariable Size 58720256 bytesDatabase Buffers 16777216 bytesRedo Buffers 667648 bytesDatabase mounted. SQL> alter session set SQL _TRACE = TRUE; Session altered. SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION; System altered. SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES = 0; System altered. SQL> ALTER SYSTEM SET AQ_TM_PROCESSES = 0; System altered. SQL> ALTER DATABASE OPEN; Database altered. SQL> set linesize 120SQL> ALTER DATABASE CHARACTER SET ZHS16GBK; ALTER DATABASE CHARACTER SET ZHS16GBK * ERROR at line 1: ORA-12721: operation cannot execute when other sessions are activeSQL> ALTER DATABASE CHARACTER SET ZHS16GBK; alter database character set ZHS16GBK * ERROR at line 1: ORA-12716: Cannot alter database character set when CLOB data exists www.2cto.com in Oracle9i, if the DATABASE has a CLOB type field, the SQL statement cannot be used to convert character sets>