A preliminary discussion on character set problem (III.)

Problem
Link

Http://www.eygle.com/special/NLS_CHARACTER_SET_03.htm

2. Change of Character Set


After database creation, if you need to modify the character set, you typically need to rebuild the database and convert it by importing the export.
We can also change it in the following ways





ALTER DATABASE CHARACTER SET




Note: Be careful when modifying the database character set, make sure to back up the database before you modify it. Because this operation cannot be rolled back, it can cause data loss or corruption.

This is the simplest way to convert a character set, but it is not always valid.
This command was introduced to Oracle at Oracle8, which essentially 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 type of conversion if the new character set is a strict superset of the old character set.
The term "superset" means:
Each character in the current character set can be represented in the new character set, and the same code point is used
For example, many character sets are a strict superset of us7ascii.

If it is not a superset, you get the following error:



sql> ALTER DATABASE CHARACTER SET zhs16cgb231280;
ALTER DATABASE CHARACTER SET zhs16cgb231280
*
ERROR at line 1:
Ora-12712:new Character set must is a superset of old character set


Let's take a look at a test (the following test is performed under Oracle9.2.0, Oracle9i is significantly different in coding than oracle8i, and the test results may vary slightly in oracle8i):

Sql> Select name,value$ from props$ where name like '%nls% '; name value$--------------------------------------------- ---------------nls_language americannls_territory americanls_currency $NLS _iso_currency americanls_numeric_ CHARACTERS., 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,212test typ=1 len=4:116,101,115,1162 rows selected.


To convert character sets, the database should be in restricted mode.




C:\>sqlplus "/As SYSDBA" Sql*plus:release 9.2.0.4.0-production on Sat Nov 1 10:52:30 2003Copyright (c) 1982, 2002, O Racle 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 Edo 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 then other sessions are Activesq l> 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 In Oracle9i, the character set is not allowed to be converted if the database has CLOB type fields sql> 

At this point, we can check the Alert<sid>.log log file to see which tables the Clob field exists on:


ALTER DATABASE CHARACTER SET ZHS16GBK
SYS. Metastylesheet (STYLESHEET)-CLOB populated
ORA-12716 signalled During:alter DATABASE CHARACTER SET zhs16gbk ...

For different situations, Oracle offers different solutions, and if it is a user datasheet, we can typically export the table containing the Clob field and drop the related object.
To import the database after the conversion, and for system tables, you can do this in the following ways:

sql> truncate TABLE metastylesheet;

Table truncated.




Then you can continue with the conversion!

Sql> ALTER session SET Sql_trace=true; Session altered. sql> ALTER DATABASE 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 americannls_territory americanls_currency $NLS _iso_currency americanls_numeric_ CHARACTERS., Nls_characterset ZHS16GBK ... Nls_nchar_characterset al16utf16nls_rdbms_version 9.2.0.4.020 rows selected. Sql> select * from Eygle.test; NAME------------------------------Test test2 rows selected.

Tips:
By setting up Sql_trace, we can track the background operations of many databases, which is one of the "sharp tools" commonly used by DBAs.
Let's take a quick look at the background processing when the database changes the character set, and I extracted the main update section.
Through the following tracking process, we see that the database changed the character set, the main update of 12 Data dictionary table, modify the original data of the database, which confirms our previous statement:
This change in character set does not essentially convert any database characters, but simply updates all the character set-related information in the database.


Update col$ Set Charsetid =: 1 Where charsetform =: 2update argument$ Set charsetid =: 1 Where charsetform =: 2update Coll ection$ Set Charsetid =: 1 Where charsetform =: 2update attribute$ Set charsetid =: 1 Where charsetform =: 2update Paramet er$ Set Charsetid =: 1 Where charsetform =: 2update result$ Set charsetid =: 1 Where charsetform =: 2update partcol$ set S Pare1 =: 1 Where charsetform =: 2update subpartcol$ Set spare1 =: 1 Where charsetform =: 2update props$ Set value$ =: 1 wh ere name =: 2update "SYS". " kotad$ "Set sys_nc_rowinfo$ =: 1 Where sys_nc_oid$ =: 2update seq$ set Increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,or Der$=:6, cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1update kopm$ Set metadata =: 1, length =: 2 where name= ' DB _fdo '



Here we are in the process of correcting a cause and the wrong way.
You can often see this method of changing the character set on the Web:

1) Use the SYS username to login Oracle.

2) view Character set content

Sql>select * from props$;

3) Modify Character Set

sql> Update props$ set value$= ' new character set ' where Name= ' Nls_characterset '

4) COMMIT;




We see a lot of people on this issue have come across a painful lesson, use this way to change the character set, if your value$ value entered the wrong character set, in the 8i then you
Database may not start, this situation is very serious, sometimes you have to recover from the backup, if it is in 9i, you can restart the database and then modify the back
Definite character set. However, we still do not recommend any database modifications in this way, which is an extremely risky operation.
In fact, when we update the character set, database startup will automatically modify the character set of the control file according to the character set of the database, if the character set can be recognized, update the control text
The character set is equal to the database character set; If the character set is not recognized, then the control file character set is updated to Us7ascii.

Modifying the character set by updating the props$ table should not be used after Oracle7.

Here are my test results, but it is strictly forbidden to do any modification studies that are not backed up, 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 americannls_territory americanls_currency $NLS _iso_currency americanls_numeric_ CHARACTERS., Nls_characterset eyglenls_calendar Gregoriannls_date_format dd-mon-rrnls_date_language AMERICAN....NLS_ Nchar_characterset zhs16gbknls_rdbms_version 8.1.7.1.118 rows selected. Restart the database and find the following actions recorded in the Alert.log file: Mon Nov 03 16:11:35 2003Updating Character set in Controlfile to Us7asciicompleted:alter DB Open Start database restore character set:sql> update p rops$ 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 americannls_territory americanls_currency $NLS _isO_currency americanls_numeric_characters., Nls_characterset Zhs16gbknls_calendar GREGORIANNLS_DATE_FORMAT Dd-mon-rrnls_date_language American ..... Nls_comp binarynls_nchar_characterset zhs16gbknls_rdbms_version 8.1.7.1.118 rows selected. The character set of the control file was updated after the database was restarted: Mon Nov 16:21:41 2003Updating character set in Controlfile to Zhs16gbkcompleted:alter DATABASE OPEN

After understanding the internal operation of character set adjustment, we can easily point out that the above method is incorrect and change the word by means of the previous "ALTER DATABASE CHARACTER SET"
When descriptor, Oracle needs to change at least 12 data dictionary tables, and this kind of direct update props$ table only completes One-twelveth of the work, the potential integrity hidden trouble is conceivable.

So, to change the character set, try to use the normal way.