Solved

Converting characterset in ORACLE 8.1.7

Posted on 2002-06-05
7
1,471 Views
Last Modified: 2010-05-19
How can I convert characterset from WE8ISO8859P1 to IW8ISO8859P8 in ORACLE 8.1.7 ?
0
Comment
Question by:avokado
7 Comments
 
LVL 2

Expert Comment

by:dbaora
ID: 7058545
Hi,
you need to recreate database with new character set.

refards,
dbaora.
0
 
LVL 22

Expert Comment

by:Helena Marková
ID: 7058604
0
 

Accepted Solution

by:
vikas1711 earned 25 total points
ID: 7058710
In most cases, you will need to do a full export/import to properly convert all data to the new character set. However, if and only if, the new character set is a strict superset of the current character set, it is possible to use the ALTER DATABASE CHARACTER SET to expedite the change in the database character set.

The target character set is a strict superset if and only if each and every codepoint in the source character set is available in the target character set, with the same corresponding codepoint value.


 Attempting to change the database character set to a character set that is not a strict superset can result in data loss and data corruption. To ensure data integrity, whenever migrating to a new character set that is not a strict superset, you must use export/import. It is essential to do a full backup of the database before using the ALTER DATABASE [NATIONAL] CHARACTER SET statement, since the command cannot be rolled back. The syntax is:

ALTER DATABASE [] CHARACTER SET ;
ALTER DATABASE [] NATIONAL CHARACTER SET
;


The database name is optional. The character set name should be specified without quotes, for example:

ALTER DATABASE CHARACTER SET WE8ISO8859P1;


To change the database character set, perform the following steps. Not all of them are absolutely necessary, but they are highly recommended:

SQL> SHUTDOWN IMMEDIATE;     -- or NORMAL
     

SQL> STARTUP MOUNT;
SQL> ALTER SYSTEM ENABLE RESTRICED SESSION;
SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE CHARACTER SET ;
SQL> SHUTDOWN IMMEDIATE;     -- or NORMAL
SQL> STARTUP;


To change the national character set, replace the ALTER DATABASE CHARACTER SET statement with ALTER DATABASE NATIONAL CHARACTER SET. You can issue both commands together if desired.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Assisted Solution

by:MEHMETUGUR
MEHMETUGUR earned 25 total points
ID: 7061515
YOU CANNOT COVET YOUR DATABASE TFROM WE8ISO8859P1 TO WE8ISO8859P8 WITH ALTER DATABABASE
P8 IS NOT A SUPERSET OF P1

EXPORT IMPORT ALSO CAUSES YOUR SOME CHARACTERS TO BE CHANGED TO ? IF YOU ACCEPT THIS TO A FULL EXPORT AND IMPORT

IF YOU ASKED FOR MORE YOU MUST HACK EXPORT FILE
YOU MUST FIRST DO A FULL EXPORT
OPEN IT WITH A HEXDECIMAL EDITOR AND CHANGE YOUR EXPORT FILE LANGUAGE FROM P1 TO P8
FIND "0x001f" IT WILL BE ON THE TOP OF THE FILE
CHANGE IT TO "0x0026"

THEN DO YOUR IMPORT THERE WILL BE NO ? BUT CHARACTERS MAY IMPORT WRONG

DO NOT PANIC AND UPDATE YOUR TABLE TO  REPLACE YOUR CHARACTERS WITH THE TRUE VALUES WITH HIGH ATTENTION ( I DO IT P1 TO P9 AND NO CHARACTERS  REQUIRED TO BE CHANGED :)), BUT YOU WILL REQUIRE )

BUT FIRST READ THE PAPER BELOW FROM ORACLE SUPPORT SITE FOR UNDERSTANDING THE EXPORT FILE CHARACTER SET

AND IF YOU ARE INEXPERIENCED ORACLE DBA DO NOT DO THIS


--------------------------------------------------
Identifying the Export Character Set:

When investigating problems like these it is useful to check the character
set used for the export. As said above, this is held in the export dump file.
It can be seen by doing a hex dump of the export file as follows (in Unix):

cat expdat.dmp | od -x | head


This will produce output similar to:

0000000 0300 0145 5850 4f52 543a 5630 372e 3033
0000020 2e30 330a 4454 534f 0a52 5441 424c 4553
0000040 0a31 3032 340a 300a 0020 2020 2020 2020
0000060 2020 2020 2020 2020 2020 2020 2020 2020
*
0000140 2020 2020 2020 2020 4d6f 6e20 4e6f 7620
0000160 3130 2031 343a 3031 3a33 3620 3139 3937
0000200 0a54 4142 4c45 2022 454d 5022 0a43 5245
0000220 4154 4520 5441 424c 4520 2245 4d50 2220


The second and the third byte in the file define the character set used for
the export.  The 16-bit value is stored big-endian, i.e. the more significant
byte first. On little-endian platforms, e.g. Sequent Dynix/PTX, the output will
be slightly different as below:

00000000 0003 4501 5058 524f 3a54 2e37 3330
etc.

In the example above, the second byte is 0x00 and the third byte is 0x01,
yielding 0x0001 as the character set ID. This shows that NLS_LANG was set
to US7ASCII during the export. The new Oracle8 functions NLS_CHARSET_NAME
and NLS_CHARSET_ID can be used to map character set IDs to character set names.
The mapping is also given in <Note 13971.1>.

The values for the most commonly used character sets are below:

Name            ID
----------------------
US7ASCII     0x0001
WE8DEC          0x0002
WE8ISO8859P1     0x001f
EE8ISO8859P2     0x0020
SE8ISO8859P3     0x0021
NE8ISO8850P4     0x0022
CL8ISO8859P5     0x0023
AR8ISO8859P6     0x0024
EL8ISO8859P7     0x0025
IW8ISO8859P8     0x0026
WE8ISO8859P9     0x0027
TH8TISASCII     0x0029
WE8ROMAN8     0x0005
WE8PC850     0x000a
US8PC437     0x0004
EE8PC852     0x0096
EE8MSWIN1250    0x00aa
CL8MSWIN1251    0x00ab
EL8MSWIN1253    0x00ae
WE8MSWIN1252    0x00b2
JA16EUC          0x033e
JA16SJIS     0x0340
ZHT16BIG5     0x0361




0
 

Expert Comment

by:ashok_khatriji
ID: 7256246
Hi,

You have provided the values for the most commonly used character sets as listed below. What is the ID for UTF8? Can u elaborate more on how to change this third byte in the export file to the value of UTF8?

Name            ID
----------------------
US7ASCII     0x0001
WE8DEC          0x0002
WE8ISO8859P1     0x001f
EE8ISO8859P2     0x0020
SE8ISO8859P3     0x0021
NE8ISO8850P4     0x0022
CL8ISO8859P5     0x0023
AR8ISO8859P6     0x0024
EL8ISO8859P7     0x0025
IW8ISO8859P8     0x0026
WE8ISO8859P9     0x0027
TH8TISASCII     0x0029
WE8ROMAN8     0x0005
WE8PC850     0x000a
US8PC437     0x0004
EE8PC852     0x0096
EE8MSWIN1250    0x00aa
CL8MSWIN1251    0x00ab
EL8MSWIN1253    0x00ae
WE8MSWIN1252    0x00b2
JA16EUC          0x033e
JA16SJIS     0x0340
ZHT16BIG5     0x0361

Thanx





0
 

Expert Comment

by:modulo
ID: 7429523
Dear: MEHMETUGUR

I've rejected your proposed answer as Experts Exchange holds an experiment to work without the answer button.

See:        http://www.experts-exchange.com/jsp/communityNews.jsp
Paragraph: Site Update for Wednesday, November 06, 2002

By this rejection the Asker will be notified by mail and hopefully he will take his responsibility to finalize the question or post an additional comment.
The Asker sees a button beside every post which says "Accept This Comment As Answer" (including rejected answers) -- so if he/she thinks yours is the best, you'll be awarded the points and the grade.

Thanks !

modulo

Community Support Moderator
Experts Exchange
0
 
LVL 5

Expert Comment

by:jpkemp
ID: 9025086
No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in the Cleanup topic area that this question is:
Split between vikas1711 & MEHMETUGUR
Please leave any comments here within the next seven days.
 
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!
 
jpkemp
EE Cleanup Volunteer
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now