|
|
有可能是Oracle 8 还不支持
看了wyq21973朋友的帖子，当时就有些疑问，因为上面我也提到从理论上讲汉字由ZHS16GBK到UTF8的转换是可能的，是不是因为wyq21973朋友用的Oracle8.1.7，还没提供这种转换呢，但由于当时也没条件试，就放了下来，最近看到论坛里关于字符集的问题又不少，于是决定做一下进一步的实验；
我的PC上本来已经装了一个数据库test1，字符集为ZHS16GBK，今天利用DBCA又建了一个test2，字符集选用UTF8。
实验所用的数据库版本为：Oracle9i Enterprise Edition Release 9.2.0.1.0
1.确认源数据库的字符集：
SCOTT@test1> select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ ---------------------------------------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
[B]NLS_CHARACTERSET ZHS16GBK[/B]
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
PARAMETER VALUE
------------------------------ ---------------------------------------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 9.2.0.1.0
20 rows selected.
注意：是ZHS16GBK。
2.确认转换前的数据：
SCOTT@test1> select deptno,dname,loc,dump(loc) from dept where deptno=50;
DEPTNO DNAME LOC
---------- -------------- -------------
DUMP(LOC)
----------------------------------------------------------------------------------------------------
[B] 50 1 1中文
Typ=1 Len=5: 49,214,208,206,196 [/B]
可以看到“中文”两个字占用4个字节（每个汉字2个字节），是ZHS16GBK编码；
3.导出源数据
D:\oracle\ora92\bin>set ORACLE_SID=test1
D:\oracle\ora92\bin>exp
Export: Release 9.2.0.1.0 - Production on Wed Mar 30 16:56:15 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Username: scott/tiger
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Enter array fetch buffer size: 4096 >
Export file: EXPDAT.DMP > dept1
(2)U(sers), or (3)T(ables): (2)U > t
Export table data (yes/no): yes >
Compress extents (yes/no): yes >
[B]Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set[/B]
About to export specified tables via Conventional Path ...
Table(T) or Partition(T:P) to be exported: (RETURN to quit) > dept
. . exporting table DEPT 8 rows exported
Table(T) or Partition(T:P) to be exported: (RETURN to quit) >
Export terminated successfully without warnings.
4.确认目标数据库的字符集
SCOTT1@test2> select * from nls_database_parameters;
PARAMETER VALUE
------------------------------------------------------------ ---------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
[B]NLS_CHARACTERSET UTF8[/B]
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
PARAMETER VALUE
------------------------------------------------------------ ---------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 9.2.0.1.0
20 rows selected.
注意：是UTF8。
5.导入目标数据库
D:\oracle\ora92\bin>set ORACLE_SID=test2
D:\oracle\ora92\bin>imp
Import: Release 9.2.0.1.0 - Production on Wed Mar 30 16:58:16 2005
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Username: scott1
Password:
Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Import file: EXPDAT.DMP > dept1
Enter insert buffer size (minimum is 8192) 30720>
Export file created by EXPORT:V09.02.00 via conventional path
Warning: the objects were exported by SCOTT, not by you
[B]import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses UTF8 character set (possible charset conversion)[/B]
List contents of import file only (yes/no): no >
Ignore create error due to object existence (yes/no): no >
Import grants (yes/no): yes >
Import table data (yes/no): yes >
Import entire export file (yes/no): no >
Username: scott
Enter table(T) or partition(T:P) names. Null list means all tables for user
Enter table(T) or partition(T:P) name or . if done: dept
Enter table(T) or partition(T:P) name or . if done: .
. importing SCOTT's objects into SCOTT1
. . importing table "DEPT" 8 rows imported
Import terminated successfully without warnings.
D:\oracle\ora92\bin>
注意上面的提示：
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
import server uses UTF8 character set (possible charset conversion)
6.检查导入数据是否正确
SCOTT1@test2> select deptno,dname,loc,dump(loc) from dept where deptno=50;
DEPTNO DNAME LOC
---------- ---------------------------- --------------------------
DUMP(LOC)
----------------------------------------------------------------------------------------------------
[B] 50 1 1中文
Typ=1 Len=7: 49,228,184,173,230,150,135 [/B]
可以看到“中文”两个字占用6个字节（每个汉字3个字节），是UTF8编码；
7.小结
[B]在Oracle 9.2环境下，汉字可以由ZHS16GBK转换为UTF8；
数据库服务器选用的字符集虽然可以各不相同，但只要各种相关设置正确，Oracle总是尽可能地将正确的转换结果呈现给客户端的用户。[/B] |
|
楼主: jeffli73
IP卡
狗仔卡
发表于 2004-12-27 09:56
显身卡
楼主
