A preliminary discussion on character set problem (vii)--internal operations on character set changes

This part is not included in the Itpub Technology series "Oracle Database DBA Technology Highlights," is later added.

As we mentioned earlier, changing the character set by modifying the props$ is an extremely dangerous way to Oracle7, and should be avoided as much as possible.

We also know that changing the character set through alter DATABASE CHARACTER set is safe and reliable, but with strict subsets and superset constraints, we rarely can actually
Use this method.

There is actually another way for Oracle to change the character set.

If you've noticed, in Oracle's Alert<sid>.log file, you might have seen this log message:

ALTER DATABASE character set Internal_convert ZHS16GBK updatin G Character set in Controlfile to ZHS16GBK SYS. snap$ (rel_query)-CLOB representation altered SYS. Metastylesheet (STYLESHEET)-CLOB representation altered SYS. external_tab$ (Param_clob)-CLOB representation altered XDB. Xdb$resource (sys_nc00027$)-CLOB representation altered ODM. ODM_PMML_DTD (DTD)-CLOB representation altered OE. Warehouses (sys_nc00003$)-CLOB representation altered PM. Online_media (sys_nc00042$)-CLOB representation altered PM. Online_media (sys_nc00062$)-CLOB representation altered PM. Online_media (Product_text)-CLOB representation altered PM. Online_media (sys_nc00080$)-CLOB representation altered PM. Print_media (Ad_sourcetext)-CLOB representation altered PM. Print_media (Ad_finaltext)-CLOB representation altered Completed:alter database character set Internal_convert ZHS1 /pre>

In this case, we see an important, Oracle command that is not publicly available:

This command is when you choose to use a typical way to create a seed database, Oracle will change the character set of the current seed database to the desired character according to the character set you choose
Set, that's what this command does.

When using this command, Oracle skips all subsets and superset checks and casts between arbitrary character sets, so you must be very careful when using this command, you must
Be aware of the risks associated with this operation.
What we've said before is still valid, and you can use Csscan to scan the entire database, if there is no serious data corruption between the converted character sets, or you can use a valid
The way you change it, you can use it to convert.
Let's take a look at the specific procedures and Oracle's internal operations:

sql> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. sql> Startup Mount ORACLE instance started. Total System Global area 135337420 bytes Fixed Size 452044 bytes Variable Size 109051904 bytes< C12/>database buffers 25165824 bytes Redo buffers the 667648 Database bytes . 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> alter session SET events ' 10046 Trace name Context Forever,level '; Session altered. Sql> ALTER DATABASE character set Internal_use zhs16cgb231280 database altered. Sql>

This is the record information in the Alert.log file:

Format the 10046 trace file and get the following information (summary):

Alter session SET Events ' 10046 Trace name context forever,level 12 'ALTER DATABASE character set Internal_use zhs16cgb231280 Call count CPU Elapsed disk query current rows---------------------------------- -------------------------------------Parse 1 0.00 0.00 0 0 0 0 Execute 1 4.88 6.04 910 16825 18099 0 Fetch 0 0.00 0.00 0 0 0 0----------------------------------------------------------------------- Total 2 4.88 6.04 910 16825 18099 0 misses in library cache during parse:1 Optimizer goal:choose parsing user Id:sys Elapsed times include waiting on following Events:event waited on Times Max. Wait total waited----------------------------------------waited----------------------control file sequential Read 4 0.00 0.00 Control File Parallel Write 2 00.08 log file Sync 2 0.08 0.08 sql*net message to Clien T 1 0.00 0.00 Sql*net Message from client 1 18.06 18.06 ******************************************************************************** ... 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 =: 2 ... update result$ set charsetid =: 1 where cha Rsetform =: 2 ... update partcol$ set spare1 =: 1 WHERE charsetform =: 2 ... update subpartcol$ set spare1 =: 1 W Here Charsetform =: 2 ....Update props$ Set value$ =: 1 WHERE name =: 2... update "SYS". Kotad___fckpd___4quot; Set sys_nc_rowinfo$ =: 1 WHERE sys_nc_oid$ =: 2 ... update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,or Der$=:6, cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1 ... update kopm$ Set metadata =: 1, length =: 2 where name= ' Db_fdo ' .... ALTER DATABASE Close NORMAL

The logs generated here you can download here (for reference):

Http://www.eygle.com/special/primary_ora_13730.zip
Http://www.eygle.com/special/primary_ora_13730.tkf.log

We see that this process is exactly the same as the internal process of the previous alter DATABASE CHARACTER set operation, meaning that the help provided by Internal_use is to make
Oracle databases bypass the checksum of subsets and superset.
This approach is useful in some ways, such as testing; apply to the product environment everyone should be extra careful, except you, no one will be responsible for the consequences:

Conclusion (we may say it again):

For DBAs, there is a very important principle: don't put your database in a dangerous position.

This requires us to do an effective backup before doing anything that might change the structure of the database, and many DBAs have learned the hard way out of the backup operation.

The author of this article:
Eygle,oracle technology concern, from China's largest Oracle technology forum Itpub.
Www.eygle.com is the author's personal site. You can contact the author by Guoqiang.Gai@gmail.com. Welcome technical discussions and exchange of links.