不同字符集的数据库之间的数据迁移问题

原创 2004年07月23日 09:12:00

准备工作(准备测试数据等):<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />

bepone> uname -a

OSF1 bepone V4.0 1229 alpha

bepone>

bepone>

bepone> sqlplus /nolog

 

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

 

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

 

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;

 

A

---------

相约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) 版权所有 1985-2000 Microsoft Corp.

 

C:/>sqlplus eygle/eygle@205

 

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

 

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

 

 

连接到:

Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production

JServer Release 8.1.7.0.0 - Production

 

SQL> select * from testnls;

 

A

---------

相约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 817exp导出数据

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

 

(c) Copyright 2000 Oracle Corporation.  All rights reserved.

 

 

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>

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

 

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

 

 

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 920imp导入数据:

$ imp eygle/eygle file=/ora/eygle

 

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

 

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

 

 

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.

$

 

 

测试效果:

 

Sql操作之二:不同数据库中表数据迁移

一:在同一服务器 insert  into   数据库name1.dbo.table_name2(字段名称1,字段名称2,……) select  字段名称1,字段名称2,…… from ...
  • llso9001
  • llso9001
  • 2016-02-02 13:57:30
  • 3334

不同字符集和不同版本的Oracle数据库迁移

标题:不同字符集、不同版本Oracle数据库迁移 一、问题描述:  Oracle11g使用的字符集为AL32UTF8, Oracle10g使用的字符集为ZHS16GBK。  要把Oracle...
  • jrq
  • jrq
  • 2012-05-23 12:08:44
  • 4648

不同数据库之间的数据迁移方案设计及迁移工具选择

以前的工作迁移过oracle到Informix、oracle和SQLSERVER、oracle到mysql。 在目前的公司又因为去o的关系,做了大量的迁移工作,栽了不少坑,所以和大家交流一下在迁移的过...
  • yincg
  • yincg
  • 2017-05-10 16:25:11
  • 6203

ETL工具实现不同数据库迁移

本人转载于  http://ainidehsj.iteye.com/blog/1735434 需求:  1.你是否遇到了需要将mysql数据库中的所有表与数据迁移到Oracle。  2....
  • qw0907
  • qw0907
  • 2017-04-13 17:10:17
  • 1580

各种数据库之间数据迁移工具

前阶段需要把sqlserver上的数据迁移到mysql上,找到了非常好用的工具DB2DB。 下载地址:http://www.szmesoft.com/DB2DB DB2DB 是目前经过测试速度最快、最...
  • sangjinchao
  • sangjinchao
  • 2017-03-20 18:01:28
  • 5141

解决不同字符集数据库数据传输中文乱码问题

有两个数据库,使用US7ASCII字符集的数据库A和使用ZHS16GBK字符集的数据库B,如果想将数据库A中的中文字插入到B数据库,直接使用insert select会导致中文乱码。 一开始考虑的解...
  • wang_san_shi
  • wang_san_shi
  • 2015-02-26 18:04:04
  • 2311

不同字符集的数据库之间的数据迁移问题

准备工作(准备测试数据等): bepone> uname -a OSF1 bepone V4.0 1229 alpha bepone> bepone> bepone> sqlplus /nolog...
  • lunar2000
  • lunar2000
  • 2004-07-23 09:12:00
  • 2128

大数据量单表在不同表名列名间的数据迁移

大数据量单表,在不同表名、列名之间的数据迁移。建议采用create table as select * from tablename的方式。...
  • u010070255
  • u010070255
  • 2017-09-05 15:58:37
  • 151

Python实现数据库之间的数据迁移

基于Python2.7的版本环境. Python实现的数据库跨服务器(跨库)迁移, 每以5000条一查询一提交, 代码中可以自行更改每次查询提交数目. # -*- coding: utf-8 -*-...
  • qq_27631797
  • qq_27631797
  • 2018-03-01 11:35:08
  • 584

解决了ORACLE数据库在不同字符集方面的转换问题

明白ORACLE的多国语言设置,ORACLE多国语言设置是为了支持世界范围的语言与字符集,一般对语言提示,货币形式,排序方式和CHAR,VARCHAR2,CLOB,LONG字段的数据的显示等有效。OR...
  • wlgyhnj
  • wlgyhnj
  • 2004-07-28 10:50:00
  • 2168
收藏助手
不良信息举报
您举报文章:不同字符集的数据库之间的数据迁移问题
举报原因:
原因补充:

(最多只允许输入30个字)