Preparation (prepare test data, etc.):
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 ('Meet 98');
1 row created.
SQL> Commit
2 ;
COMMIT COMPLETE.
SQL> SELECT * from testnls;
A
---------
Comparison 98
SQL> EXIT
Disconnected from Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
Jserver Release 8.1.7.0.0 - Production
BEPONE>
Check from the client to see if it is normal:
Microsoft Windows 2000 [Version 5.00.2195]
(C) All rights reserved 1985-2000 Microsoft Corp.
C: /> SQLPlus Eygle / Eygle @ 205
SQL * Plus: Release 9.2.0.1.0 - Production On Wednesday, January 23 15:43:22 2002
CopyRight (C) 1982, 2002, 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
SQL> SELECT * from testnls;
A
---------
Comparison 98
SQL>
Verify two methods of the server character set:
method one:
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>
Method Two:
Export data using 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
(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 Convenctional 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/Orcle/eygle.dmp /data/oracle/eygletest.dmp
/Data/oracle/eygle.log /data/oracle/eygletest.log
Open this DMP file using Utledit, find the 2nd and 3rd bytes:
SQL> SELECT NLS_CHARSET_NAME (TO_NUMBER ('0354', 'xxxx')) from DUAL
NLS_CHARSET_NAME (to_number ('03
----------------------------------------
ZHS16GBK
SQL>
Transfer this DMP to the remote host: 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 model 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 model 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 model 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
$
View the character set of the remote database:
$ 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
connected.
SQL> SELECT NLS_CHARSET_NAME (TO_NUMBER ('0367', 'xxxx')) from Dual;
NLS_CHARSET_NAME (TO_NUMBER ('0367', 'XXXX'))
-------------------------------------------------- ----------------
UTF8
SQL>
Use the Edit Tool to modify the DMP character set:
Change the second and third bytes of Eygle.dmp from 0354 to 0367
Use Oracle 920's IMP import data:
$ 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.
$
Test effect: