Re: How do I migrate from US7ASCII on 8.0.5 to UTF8 on 8i

In article <91cg2t$9uo$1_at_nnrp1.deja.com>,   Franco Finstad <ffinstad_at_globalsight.com> wrote:
> What is the best way to migrate my data from a US7ASCII 8.0.5 install
> to UTF8 8i install? My data is mostly multi-lingual text -- English,
> Spanish, Japanese, Chinese.
>
> I tried to export from the 8.0.5 database to a .dmp file and them
> import that file to the 8i database but got the following error:
>
> IMP-00016: required character set conversion (type 1 to 871) not
> supported
> IMP-00000: Import terminated unsuccessfully
>
> Any help is greatly appreciated.
>
> --
> Franco Finstad
>
> Sent via Deja.com
> http://www.deja.com/
>

Franco,

I would suggest looking at the National Language Support guide in your documentation (in 8.1.x it's a separate document, and I think it's been that way in the past). If you don't have hardcopies of the doc's, get them off your installation CD.

Export/Import won't help you in this case because as far as Oracle's concerned the US7ASCII character set only involves 7 bits. Oracle stores 8-bits, but he doesn't think you should care what that 8th bit is, so he feels free to disregard it in things like export/import.

You can change the character set without rebuilding the database, but this only works if your target character set is a strict superset of your current character set. According to my 8.1.6 documentation, UTF8 is a strict superset of US7ASCII, so you may be OK.

The basic steps documented are in the NLS Guide are:

SQL> SHUTDOWN IMMEDIATE; -- or NORMAL

    <do a full backup>

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

I would also suggest doing an
SQL> ALTER DATABASE NATIONAL CHARACTER SET <new_character_set_name>; after the base CHARACTER SET.

I recently changed character sets on some of my databases from US7ASCII to WE8ISO8859P1 (another superset of US7ASCII) and had no trouble.

I strongly urge you to consult your specific version of the documentation before moving forward with this change.

HTH. Matt.

Sent via Deja.com
http://www.deja.com/ Received on Fri Dec 15 2000 - 09:33:36 CST