Oracle Character set knowledge that Oracle DBA needs to know

As an Oracle DBA, a number of problems with character sets are frequently dealt with at work. But when you really want to write something about it, you suddenly don't have a clue. After a half-day, or decided to use the two character set as an example of the point of entry, it is a clue, perhaps in the course of the experiment, the problem will emerge one after another.

Now, let's cut to the chase.

The database I use is oracle10.2.0.3, and the database character set is Al32utf8.

The client is Windows XP for the same machine.

Here is an example of a demo:

[PHP]

sql> drop table test purge;

Table dropped.

Sql> CREATE TABLE Test (col1 number (1), col2 varchar2 (10));

Table created.

[/php]

--session 1 Sets the client character set to ZHS16GBK (CharacterSet for ZHS16GBK that modifies the registry Nls_lang entry) to insert two Chinese characters into the table.

[PHP]

sql> INSERT INTO test values (1, ' China '); --1 is the mark for session 1

1 row created.

Sql> commit;

Commit complete.

[/php]

--session 2 Sets the client character set Al32utf8 (the CharacterSet of the registry Nls_lang entry is Al32utf8), the same as the database character set. Inserts two and 1 Chinese characters in the table.

[PHP]

sql> INSERT INTO test values (2, ' China '); --2 is the mark for session 2

1 row created.

Sql> commit;

Commit complete.

--session 1

Sql> select * from test;

COL1 COL2

---------- --------------------

2???

1 China

--session 2

Sql> select * from test;

COL1 COL2

---------- ----------

2 China

1 Juan Ricoh

[/php]

As you can see from the results of Session 1 and Session 2, the same characters (note, I mean what we see, shown as the same characters), are displayed as garbled in different character set input environments.

In the ZHS16GBK character set client, we see the UTF8 character set client input the same Chinese into a garbled-->col1=2 col2 field

In the UTF8 character set client, we see the ZHS16GBK character set of the client-entered Chinese into a different character-->col1=1 col2 field

From this example, we seem to feel something wrong, may also be associated with the real environment in the garbled problem.

The problem seems to have a way of thinking, OK, let's continue to do the experiment:

[PHP]

--session 1 (or Session 2, it doesn't matter here)

Sql> Select Col1,dump (col2,1016) from test;

COL1

----------

DUMP (col2,1016)

--------------------------------------------------------------------------------

2

Typ=1 len=4 CHARACTERSET=AL32UTF8:D6,D0,B9,FA

1

Typ=1 len=6 CHARACTERSET=AL32UTF8:E4,B8,AD,E5,9B,BD

[/php]