Home » RDBMS Server » Server Administration » Is it possible to convert database characterset? (Oracle 11g, any platform)
Is it possible to convert database characterset? [message #674788] Sun, 17 February 2019 20:24 Go to next message
trantuananh24hg
Messages: 741
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Hi,

I owned the Database which was created in AL32UTF8, then now, some characters words did not show correctly because of the source data was in AL16UTF16.

Can I convert my Database from AL32UT8 to AL16UTF16?

Thank you
Re: Is it possible to convert database characterset? [message #674790 is a reply to message #674788] Mon, 18 February 2019 00:43 Go to previous messageGo to next message
Michel Cadot
Messages: 66728
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

No, you have to create a new one.
But the main problem does not come from the database: with AL16UTF16 you should be able to store the same characters than with AL32UTF8.
Most likely the problem comes from wrong NLS_LANG settings.

Re: Is it possible to convert database characterset? [message #674791 is a reply to message #674790] Mon, 18 February 2019 00:51 Go to previous messageGo to next message
trantuananh24hg
Messages: 741
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you so much, Michel!
Re: Is it possible to convert database characterset? [message #674794 is a reply to message #674788] Mon, 18 February 2019 01:53 Go to previous messageGo to next message
John Watson
Messages: 8086
Registered: January 2010
Location: Global Village
Senior Member
I think you can convert frpm 11 onwards, there is a routine here,
https://docs.oracle.com/cd/E11882_01/server.112/e10729/
the risk is that some data might be damaged in the process
Re: Is it possible to convert database characterset? [message #674796 is a reply to message #674794] Mon, 18 February 2019 02:17 Go to previous messageGo to next message
Michel Cadot
Messages: 66728
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

You can never convert a database from/to UTF8 using ALTER DATABASE, you always have to recreate the database in this case and use export/import. The reason is that some dictionary character data are not in the 0-127 range which is common to UTF8 and all the other character sets, so some characters have to be converted with code points with different sizes and this can't be done in place.

But as I said, as the characters covered by AL16UTF16 and AL32UTF8 are the same ones (with different code points of course), the original problem does not reside in the database character set but on how the character were inserted in the database.

Re: Is it possible to convert database characterset? [message #674832 is a reply to message #674796] Mon, 18 February 2019 19:50 Go to previous message
trantuananh24hg
Messages: 741
Registered: January 2007
Location: Ha Noi, Viet Nam
Senior Member
Thank you Michel and John,

I knew I can not convert already UTF8 orignial database to another by alter command, in deed. Then I tried to use export/import, but I failed becaused the points of different bit (a term bit as I don't know how to descript). Many characters was different of length to another.

Thank you again,
Previous Topic: OJVM patch January 2019
Next Topic: XE 18c for Windows
Goto Forum:
  


Current Time: Mon Dec 16 04:11:50 CST 2019