不同字符集的数据库之间的数据迁移问题_oracle不同字符集数据迁移-优快云博客

本文链接：https://blog.youkuaiyun.com/lunar2000/article/details/49462

本文详细描述了如何处理不同字符集的Oracle数据库之间的数据迁移问题，包括创建测试数据、检查字符集、使用exp和imp工具，并强调了在迁移过程中可能遇到的字符集转换问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

准备工作（准备测试数据等）：

bepone> uname -a

OSF1 bepone V4.0 1229 alpha

bepone>

bepone> sqlplus /nolog

SQL*Plus: Release 8.1.7.0.0 - Production on Thu Jan 23 15:24:12 2003

SQL> conn eygle/eygle

Connected.

SQL> create table testnls(a char(9));

Table created.

SQL> insert into testnls values('相约98');

1 row created.

SQL> commit

2 ;

Commit complete.

SQL> select * from testnls;

---------

相约98

SQL> exit

Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production

JServer Release 8.1.7.0.0 - Production

bepone>

从客户端检查一下显示是否正常：

Microsoft Windows 2000 [Version 5.00.2195]

C:/>sqlplus eygle/eygle@205

SQL*Plus: Release 9.2.0.1.0 - Production on 星期三 1月 23 15:43:22 2002

连接到:

Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production

JServer Release 8.1.7.0.0 - Production

SQL> select * from testnls;

---------

相约98

SQL>

验证服务器字符集的两个方法：

方法一：

bepone> env | grep NLS

NLS_LANG=American_america.zhs16gbk

ORA_NLS33=/data/oracle/app/oracle/product/8.1.7/ocommon/nls/admin/data

bepone>

SQL> SELECT VALUE$ FROM PROPS$

2 WHERE NAME='NLS_NCHAR_CHARACTERSET';

VALUE$

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

ZHS16GBK

SQL>

方法二：

使用oracle 817的exp导出数据

bepone> exp eygle/eygle file=/data/oracle/eygle log=/data/oracle/eygle rows=y

Export: Release 8.1.7.0.0 - Production on Thu Jan 23 16:06:51 2003

Connected to: Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production

JServer Release 8.1.7.0.0 - Production

Export done in ZHS16GBK character set and ZHS16GBK NCHAR character set

. exporting pre-schema procedural objects and actions

. exporting foreign function library names for user EYGLE

. exporting object type definitions for user EYGLE

About to export EYGLE's objects ...

. exporting database links

. exporting sequence numbers

. exporting cluster definitions

. about to export EYGLE's tables via Conventional Path ...

. . exporting table TEST 3 rows exported

. . exporting table TESTNLS 1 rows exported

. exporting synonyms

. exporting views

. exporting stored procedures

. exporting operators

. exporting referential integrity constraints

. exporting triggers

. exporting indextypes

. exporting bitmap, functional and extensible indexes

. exporting posttables actions

. exporting snapshots

. exporting snapshot logs

. exporting job queues

. exporting refresh groups and children

. exporting dimensions

. exporting post-schema procedural objects and actions

. exporting statistics

Export terminated successfully without warnings.

bepone>

bepone> cd /data/oracle

bepone> ls /data/oracle/eygle*

/data/oracle/eygle.dmp /data/oracle/eygletest.dmp

/data/oracle/eygle.log /data/oracle/eygletest.log

使用utledit打开这个dmp文件，找到第2个和第3个字节：

SQL> select nls_charset_name(to_number('0354','xxxx')) from dual;

NLS_CHARSET_NAME(TO_NUMBER('03

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

ZHS16GBK

SQL>

传输这个dmp到远程主机：

bepone> ftp hpl1

Connected to hpl1.

220 hpl1 FTP server (Version 1.1.214.4(PHNE_23950) Tue May 22 05:49:01 GMT 2001) ready.

Name (hpl1:oracle): oracle

331 Password required for oracle.

Password:

230 User oracle logged in.

Remote system type is UNIX.

Using binary mode to transfer files.

ftp> bin

200 Type set to I.

ftp> prompt *

Interactive mode off.

ftp> cd /ora

250 CWD command successful.

ftp> mput eygle*

local: eygle.dmp remote: eygle.dmp

200 PORT command successful.

150 Opening BINARY mode data connection for eygle.dmp.

226 Transfer complete.

16384 bytes sent in 0.00083 seconds (1.9e+04 Kbytes/s)

local: eygle.log remote: eygle.log

200 PORT command successful.

150 Opening BINARY mode data connection for eygle.log.

226 Transfer complete.

1206 bytes sent in 0 seconds (1.2 Kbytes/s)

local: eygletest.dmp remote: eygletest.dmp

200 PORT command successful.

150 Opening BINARY mode data connection for eygletest.dmp.

226 Transfer complete.

16384 bytes sent in 0.016 seconds (1e+03 Kbytes/s)

local: eygletest.log remote: eygletest.log

200 PORT command successful.

150 Opening BINARY mode data connection for eygletest.log.

226 Transfer complete.

1155 bytes sent in 0 seconds (1.1 Kbytes/s)

ftp> bye

221 Goodbye.

bepone>

$ hostname

hpl1

$ uname -a

HP-UX hpl1 B.11.11 U 9000/800 1124444645 unlimited-user license

$ cd /ora

$ ls eygle*

eygletest.dmp eygletest.log

查看远程数据库的字符集：

$ env | grep NLS

NLS_LANG=american_america.UTF8

ORA_NLS33=/ora/ocommon/nls/admin/data

$ sqlplus "/ as sysdba"

SQL*Plus: Release 9.2.0.1.0 - Production on Thu Jan 23 15:49:42 2003

Connected to:

Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production

With the Partitioning and Real Application Clusters options

JServer Release 9.2.0.1.0 - Production

SQL> conn eygle/eygle@hpcin

已连接。

SQL> select nls_charset_name(to_number('0367','xxxx')) from dual;

NLS_CHARSET_NAME(TO_NUMBER('0367','XXXX'))

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

UTF8

SQL>

使用编辑工具修改dmp的字符集：

把eygle.dmp中的第二和第三个字节改从 0354 为 0367

使用oracle 920的imp导入数据：

$ imp eygle/eygle file=/ora/eygle

Import: Release 9.2.0.1.0 - Production on Thu Jan 23 16:07:31 2003

Connected to: Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production

With the Partitioning and Real Application Clusters options

JServer Release 9.2.0.1.0 - Production

Export file created by EXPORT:V08.01.07 via conventional path

import done in UTF8 character set and AL16UTF16 NCHAR character set

import server uses WE8ISO8859P1 character set (possible charset conversion)

export server uses ZHS16GBK NCHAR character set (possible ncharset conversion)

. importing EYGLE's objects into EYGLE

. . importing table "TEST" 3 rows imported

. . importing table "TESTNLS" 1 rows imported

Import terminated successfully without warnings.

测试效果：