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

在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个字节的值来修改字符集，达到正常导入数据的方法

已经失效。