How to convert Oracle 8.1.7 CHARACTER SET?

[ericfjchen]

Hi,

This is Oracle 8.1.7 database. We would like to convert its CHARACTER SET. Besides exp/imp, is there any other way to finish it?

Thanks

FJ

[Steven E. Protter]

I don't know of any other way. Is exp/imp a problem.

There may be a converson procedre at one of these two sites:

http://technet.oracle.com
http://metalink.oracle.com

SEP

[Indira Aramandla · Accepted solution]

Hi,

Convert Oracle database CHARACTER SET.

There are 2 basic ways of changing the character set and a third 'combined' way:

1. Using the "ALTER DATABASE CHARACTER SET" command
This is not always possible because this does not change the actual code points of the stored data. So this method can only be used if the data that is currently stored in the database is defined under the same code points in the new character set.

2. Using Export/Import
This will always work, you simply export the current database, then create a new database with the new character set and import the data into that database. Of course the characters that you were storing will still have to be defined in the new character set for this to work!

3. Using a combination of ALTER DATABASE CHARACTER SET and export/import In some cases method 1 does not work because csscan tells you that some data needs to be converted to the new character set, and method 2 will simply take too much time. In those cases it is usualy possible to use a combination of the 2 methods:

a) Export the data from the tables that need to be converted
b) Truncate or drop those tables.
c) Run csscan again to confirm that all data is now ready to be moved to the new character set directly and if that is the case change the character set of the database using the ALTER DATABASE CHARACTERSET command (method 1).
d) Now that the character set has changed we can simply import the data exported in step (a). The import will convert that data so that it gets stored in the correct way for this character set.

Note: There are some additional considerations when you change the character set of an Oracle Applications database, please see the following note for a complete overview of those:

In Metalink there are notes for changing the database character set using the command â ALTER DATABASE CHARACTER SETâ for changing from the following

Changing from WE8ISO8859P1 to WE8ISO8859P15, Changing from EE8ISO8859P2 to EE8MSWIN1250, Changing from AR8ISO8859P6 to AR8MSWIN1256, Changing from EL8ISO8859P7 to EL8MSWIN1253, Changing WE8ISO8859P9 to WE8ISO8859P1/WE8MSWIN1252, Changing WE8ISO8859P15 to WE8MSWIN1252,
Changing from WE8MSWIN1252 to WE8ISO8859P15, Changing from WE8ISO8859P15 TO WE8ISO8859P1, Changing from WE8DEC to WE8ISO8859P1/WE8MSWIN1252, Changing from WE8ISO8859P1/WE8ISO8859P15 or WE8MSWIN1252 to UTF8, Changing from AL24UTFFSS to UTF8 - AL32UTF8


In

[Yogeeraj_1]

hi FJ,

see also: "Changing the Character Set After Database Creation" available at url: http://download-east.oracle.com/docs/cd/A81042_01/DOC/server.816/a76966/ch3.htm#47136

hope this helps!

regards
yogeeraj

[Ganesha Sridhara]

Ref : Globalization Guide http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96529/ch10.htm#1009580

------------ Example -----------


STARTUP MOUNT;


ALTER SYSTEM ENABLE RESTRICTED SESSION;


ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0;


ALTER SYSTEM SET AQ_TM_PROCESSES=0;


ALTER DATABASE OPEN;


ALTER DATABASE CHARACTER SET new_character_set;


SHUTDOWN IMMEDIATE; -- or SHUTDOWN NORMAL;


STARTUP;

------------ End Example -----------

Justin
Distributed Database Consulting, Inc.
http://www.ddbcinc.com/askDDBC