LINK:
http://www.eygle.com/special/nls_character_set_03.htm
2. Character set change
After the database is created, if you need to modify the character set, you usually need to rebuild the database and convert it by importing the exported manner. We can also change the following ways
Alter Database Character Set
Note: You must be cautious when modifying the database character set, and must be backed up for the database before modification. Since it is not necessary to fall back, it may cause data loss or damage. This is the simplest conversion character set, but it is not always valid. This command is introduced to Oracle when Oracle 8, which does not convert any database characters in nature, just a simple update of all follow-up information related to character sets.
This means that you can only use this way to convert in the case where the new character set is the old character set. The so-called super-collection refers to: Each character in the current character set can be represented in the new character set, and using the same code point, such as many character sets are strict superchard of US7ASCII.
If it is not a supercoming, the following error will be obtained:
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
Let's take a look at a test (the following test is performed in Oracle9.2.0, and Oracle9i has a large change in the coding in Oracle8i, in Oracle8i, the test results may be slightly different):
SQL> SELECT NAME, VALUE $ from Props $ Where Name Like '% NLS%'
Name Value $
------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS.,
NLS_CharacterSet US7ASCII
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
.................
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 9.2.0.4.0
20 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.
Converting the character set, the database should be done in the restrical mode.
C: /> SQLPLUS "/ as sysdba"
SQL * Plus: Release 9.2.0.4.0 - Production On Sat NOV 1 10:52:30 2003 Copyright (C) 1982, 2002, Oracle Corporation. All Rights Reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production
With the partitioning, Oracle Label Security, OLAP AND ORACLE DATA MINING OPTIONS
JServer Release 9.2.0.4.0 - Production
SQL> Shutdown Immediate
Database closed.
Database dismount.
Oracle Instance Shut Down.
SQL> Startup Mount;
Oracle Instance Started.
Total System Global Area 76619308 Bytes
Fixed size 454188 bytes
Variable size 58720256 bytes
Database buffers 16777216 BYTES
Redo buffers 667648 bytes
Database mounted.
SQL> ALTER session set SQL_TRACE = true;
Session altered.
SQL> ALTER System Enable Restricted Session;
SYSTEM altered.
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESS = 0;
SYSTEM altered.
SQL> ALTER SESTEM SET AQ_TM_PROCESS = 0;
SYSTEM altered.
SQL> ALTABASE OPEN;
Database altered.
SQL> SET LINESIZE 120
SQL> ALTABASE Character set zhs16gbk;
Alter Database Character Set ZHS16GBK
*
Error At Line 1:
ORA-12721: Operation Cannot Execute When Other Sessions Are Active
SQL> ALTABASE Character set zhs16gbk;
Alter Database Character Set ZHS16GBK
*
Error At Line 1:
ORA-12716: Cannot Alter Database Character Set When Clob Data EXISTS
In Oracle9i, if the data inventory is in the clob type field, then the character set is not allowed to convert the character set.
SQL>
At this time, we can go to view the Alert
Alter Database Character set zhs16gbk sys.metastylesheet - Clob Populatedora-12716 Signalled Database: Alter Database Character Set ZHS16GBK ...
For different situations, Oracle provides different solutions, if it is a user data table, usually we can export the table containing the clob field, then DROP off the relevant object, then import the database; for the system table, you can follow: SQL> Truncate Table MetastyLesheet;
Table truncated.
Then you can continue to conversion!
SQL> ALTER session set SQL_TRACE = true;
Session altered.
SQL> ALTABASE Character set zhs16gbk;
Database altered.
SQL> ALTER session set SQL_TRACE = false;
Session altered.
In 9.2.0, after the conversion is complete, you can rebuild the MetastyLesheet table by running the CATMET.SQL script:
SQL> @? / Rdbms / admin / catmet.sql
Converted data:
SQL> SELECT NAME, VALUE $ from Props $ Where Name Like '% NLS%'
Name Value $
------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS.,
NLS_CharacterSet ZHS16GBK
... ..
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 9.2.0.4.0
20 rows selected.
SQL> SELECT * from Eygle.test;
Name
------------------------------
test
Test
2 rows selected.
Tip: By setting SQL_TRACE, we can track the background operation of many databases, this tool is one of the "weapons" commonly used by DBA. We briefly look at the background processing of the database to change the character set, I have extracted the main update part. Through the following tracking process, we see the database when changing the character set, mainly updated 12 data dictionary tables, modified the original data of the database, which also confirmed our previous statement: This change character set is in essentially Do not convert any database characters, just a simple update information related to all follow-up sets in the database.
Update COL $ set charsetid =: 1
WHERE
Charsetform =: 2
Update Argument $ set charsetid =: 1
WHERE
Charsetform =: 2
Update Collection $ SET CharsetId =: 1
WHERE
Charsetform =: 2
Update Attribute $ set charsetid =: 1
WHERE
Charsetform =: 2
Update Parameter $ set charsetid =: 1
WHERE
Charsetform =: 2UPDATE RESULT $ SET CharsetId =: 1
WHERE
Charsetform =: 2
Update partcol $ set spare1 =: 1
WHERE
Charsetform =: 2
Update SubpartCOL $ SET SPARE1 =: 1
WHERE
Charsetform =: 2
Update Props $ SET VALUE $ =: 1
WHERE
Name =: 2
Update "sys". "KOTAD $" set sys_nc_rowinfo $ =: 1
WHERE
SYS_NC_OID $ =: 2
Update SEQ $ SET INCREMENT $ =: 2, minvalue =: 3, maxvalue =: 4, cycle # =: 5, Order $ =: 6,
Cache =: 7, highwater =: 8, Audit $ =: 9, Flags =: 10
WHERE
Obj # =: 1
Update kopm $ set metadata =: 1, length =: 2
WHERE
Name = 'db_fdo'
Here we are in a way to correct a source and error method. You can often see such a change in the character set on the Internet:
1) Log in to Oracle with the SYS username.
2) View character set content
SQL> SELECT * from ProPs $;
3) Modify the character set
SQL> Update Props $ set value $ = 'new character set' where name = 'nls_characterset'
4) commit;
We see that many people have encountered a painful lesson on this issue, using this way to change the character set, if your value is entered in the wrong character set, then your database may not start in 8i, this The situation is very serious, sometimes you have to recover from the backup; if it is in 9i, you can restart the database before modify the correct character set. But we still do not recommend using this way to make any database changes, which is an extremely dangerous operation. In fact, when we update the character set, the database will automatically modify the character set of the control file according to the character set of the database, if the character set can be identified, the update control file character set is equal to the database character set; if the character set is not recognized, then Control file character set is updated to US7ASCII.
Modify the character set by updating the PROPS $ table, and should not be used after Oracle7.
The following is my test results, but it is strictly forbidden to make a modification study that is not backup, even for the test library.
SQL> Update Props $ SET VALUE $ = 'Eygle' Where Name = 'NLS_CHARACTERSET'
1 row updated.
SQL> commit;
COMMIT COMPLETE.
SQL> SELECT NAME, VALUE $ from Props $ Where Name Like '% NLS%'
Name Value $
------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS.,
NLS_Characterset Eygle
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
.
NLS_NCHAR_CHARACTERSET ENS16GBK
NLS_RDBMS_VERSION 8.1.7.1.1
18 rows selected.
Restart the database and discover the following operations in the alert.log file:
Mon Nov 03 16:11:35 2003
Updating Character Set in ControlFile To US7ASCII
Completed: Alter Database Open
Start the database and restore the character set setting:
SQL> Update Props $ SET VALUE $ = 'zhs16gbk' Where name = 'nls_characterset';
1 row updated.
SQL> commit;
COMMIT COMPLETE.
SQL> SELECT NAME, VALUE $ from Props $ Where Name Like '% NLS%'
Name Value $
------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS.,
NLS_CharacterSet ZHS16GBK
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
.........
NLS_Comp Binary
NLS_NCHAR_CHARACTERSET ENS16GBK
NLS_RDBMS_VERSION 8.1.7.1.1
18 rows selected.
After restarting the database, the character set of the control file is updated:
Mon Nov 03 16:21:41 2003
Updating character set in controlfile to zhs16gbk
Completed: Alter Database Open
After understanding the internal operation of the character set, we can easily point out that the above method is incorrect. When changing the character set in the previous "Alter Database Character Set" mode, Oracle needs to change the 12 data dictionary table, and this The way to update the PROPS $ table only completed one of the work of one of the work, and potential integrity hidden hazards can be known.
So, changing the character set is trying to use the normal way.
