ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 无法通过修改DMP文件头的第2、3个字节来导入不同字符集的汉字

无法通过修改DMP文件头的第2、3个字节来导入不同字符集的汉字

原创 Linux操作系统 作者:ZALBB 时间:2019-07-06 15:21:05 0 删除 编辑

在8、8I里,当遇上汉字需要在不同字符集的数据库里转换导人、导出时,我们需要更改DMP文件的第2、3个字节来“欺骗”

EXP,来达到导入汉字的目的。但是,这9206里,这种方法已经失效,以下是测试过程。


环境:WINDOWS2000SERVER, 9206。
机器上创建两数据库,数据库A,字符集为US7ASCII,数据库B,字符集为ZHS16GBK

1、数据库A,设置环境变量
Microsoft Windows 2000 [Version 5.00.2195]
(C) 版权所有 1985-2000 Microsoft Corp.

D:ORACLEBIN>set nls_lang=american_america.us7ascii

D:ORACLEBIN>set oracle_sid=emc

D:ORACLEBIN>sqlplus

SQL*Plus: Release 9.2.0.6.0 - Production on Thu Apr 28 11:12:17 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
emc

SQL> select value$ from props$ where name='NLS_CHARACTERSET';

VALUE$
------------------------------------------------------------------------------
US7ASCII

SQL> connect scott/tiger
Connected.
SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE

2、创建一新表,插入汉字
SQL> create table ascii(name varchar2(20));

Table created.

SQL> insert into ascii select '只能' from dual;

1 row created.

SQL> commit;

Commit complete.

SQL> select * from ascii;

NAME
--------------------
只能

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

3、重新登录测试汉字是否正常显示。
D:ORACLEBIN>sqlplus

SQL*Plus: Release 9.2.0.6.0 - Production on Thu Apr 28 11:13:57 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name: scott/tiger

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> select * from ascii;

NAME
--------------------
只能

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

4、EXP该表
D:ORACLEBIN>exp file=h:tempemc.dmp tables=ascii

Export: Release 9.2.0.6.0 - Production on Thu Apr 28 11:25:15 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.


Username: scott/tiger

Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production
Export done in US7ASCII character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...
. . exporting table ASCII 1 rows exported
Export terminated successfully without warnings.

D:ORACLEBIN>


5、复制emc.dmp文件为另外一文件emc2.dmp,之后更改该文件的第2、3个字节为 03 54

6、数据库B,字符集ZHS16GBK
D:ORACLEBIN>set oracle_sid=psi2

D:ORACLEBIN>set nls_lang=american_america.zhs16gbk

D:ORACLEBIN>imp file=h:tempemc2.dmp tables=ascii

Import: Release 9.2.0.6.0 - Production on Thu Apr 28 14:26:45 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Username: scott/1

Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

7、正常导入数据库
Export file created by EXPORT:V09.02.00 via conventional path
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SCOTT's objects into SCOTT
. . importing table "ASCII" 1 rows imported
Import terminated successfully without warnings.

8、查看数据库,发现汉字乱码。
D:ORACLEBIN>sqlplus

SQL*Plus: Release 9.2.0.6.0 - Production on Thu Apr 28 14:26:51 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name: scott/1

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> select * from ascii;

NAME
--------------------
????

9、该数据库的字符集
SQL> connect / as sysdba
Connected.
SQL> select value$ from props$ where name='NLS_CHARACTERSET';

VALUE$
--------------------------------------------------------------------------------
ZHS16GBK

10、往该表里插入汉字,以确认是否可正常存储汉字
SQL> connect scott/1
Connected.
SQL> select * from ascii;

NAME
--------------------
????

SQL> insert into ascii select '可以' from dual;

1 row created.

SQL> commit;

Commit complete.

SQL> exit
Disconnected from Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

11、退出后再登录查询。
D:ORACLEBIN>sqlplus

SQL*Plus: Release 9.2.0.6.0 - Production on Thu Apr 28 14:39:15 2005

Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.

Enter user-name: scott/1

Connected to:
Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options
JServer Release 9.2.0.6.0 - Production

SQL> select * from ascii;

NAME
--------------------
????
可以

SQL>

说明在9206里,通过更改dmp文件的第2、3个字节的值来修改字符集,达到正常导入数据的方法
已经失效。

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/1112/viewspace-103701/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2018-08-15

  • 博文量
    46
  • 访问量
    34490