Home » RDBMS Server » Server Administration » Problem convertion of AL32UTF8 TO AL16UTF16
Problem convertion of AL32UTF8 TO AL16UTF16 [message #243865] Sun, 10 June 2007 03:40 Go to next message
emadbsb
Messages: 334
Registered: May 2005
Location: egypt
Senior Member
Hii All

I install oracle 10g XE (Express Edition)
it is installed using characterset AL32UTF8
i want to change it to AL16UTF16

I tried to change it by connecting to the database as sysdba by the user sys and after changing it and restarting the computer the database become down and i reinstalled Oracle 10gXE again

How can i solve this problem because i have a dump file that is of characterset AL16UTF16 and i want to import it
and there is many rows in some tables failed to be imported

Please help me in solving this problem

Thanks for everyone helped and helping me

Report message to a moderator

Re: Problem convertion of AL32UTF8 TO AL16UTF16 [message #243867 is a reply to message #243865] Sun, 10 June 2007 07:44 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Post exactly what you've done.
That is copy and paste your screen.

For instance, you said:
Quote:
I tried to change it by connecting to the database as sysdba by the user sys and after changing it

How did you that? It is not directly possible.

Quote:
How can i solve this problem because i have a dump file that is of characterset AL16UTF16 and i want to import it

You can do it in AL32UTF8 db, there is no problem.
Quote:
there is many rows in some tables failed to be imported

Which ones? What was the errors?

Too many questions. No one can answer.
I don't understand that after more 300 posts you don't know that.

Regards
Michel

Report message to a moderator

Re: Problem convertion of AL32UTF8 TO AL16UTF16 [message #243937 is a reply to message #243865] Mon, 11 June 2007 01:26 Go to previous messageGo to next message
emadbsb
Messages: 334
Registered: May 2005
Location: egypt
Senior Member
For this :
How did you that? It is not directly possible.

I connect to the database as sysdba and updated in tableprop$ that contain the parameter of NLS_CHARACTERSET
and changed its value to AL16UTF16

For this :
You can do it in AL32UTF8 db, there is no problem.

the row that have fields contain arabic letters when importing its tables it is not imported because of the new characterset
e.g
suppose there is a column named DescA of datatype Varchar2(10)
when i insert to it under the characterset AL16UTF16
i can insert up to 10 character
while when inserting to it under the characterset AL32UTF8
i can insert up to 5 character

inserting more than 5 raising an error that have a mean of
inserted value too large for column
and that was the answer of 

Which ones? What was the errors?

[Updated on: Mon, 11 June 2007 01:27]

Report message to a moderator

Re: Problem convertion of AL32UTF8 TO AL16UTF16 [message #243947 is a reply to message #243937] Mon, 11 June 2007 01:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
I connect to the database as sysdba and updated in tableprop$ that contain the parameter of NLS_CHARACTERSET
and changed its value to AL16UTF16

Then you lost your database.

Quote:
the row that have fields contain arabic letters when importing its tables it is not imported because of the new characterset
e.g
suppose there is a column named DescA of datatype Varchar2(10)
when i insert to it under the characterset AL16UTF16
i can insert up to 10 character
while when inserting to it under the characterset AL32UTF8
i can insert up to 5 character

After recreating your database, change all the [VAR]CHAR definition from BYTE to CHAR length semantics, then you will no more have any problem.

Regards
Michel

Report message to a moderator

Re: Problem convertion of AL32UTF8 TO AL16UTF16 [message #243955 is a reply to message #243865] Mon, 11 June 2007 02:05 Go to previous messageGo to next message
emadbsb
Messages: 334
Registered: May 2005
Location: egypt
Senior Member
for this :
Quote:
After recreating your database, change all the [VAR]CHAR definition from BYTE to CHAR length semantics, then you will no more have any problem.


How can i change all the Varchar definition from BYTE to CHAR

[Updated on: Mon, 11 June 2007 02:46] by Moderator

Report message to a moderator

Re: Problem convertion of AL32UTF8 TO AL16UTF16 [message #243962 is a reply to message #243955] Mon, 11 June 2007 02:11 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
alter table ... modify (... varchar2(... CHAR))

Regards
Michel

Report message to a moderator

Re: Problem convertion of AL32UTF8 TO AL16UTF16 [message #243970 is a reply to message #243865] Mon, 11 June 2007 02:23 Go to previous messageGo to next message
emadbsb
Messages: 334
Registered: May 2005
Location: egypt
Senior Member
du you mean my tables
if you mean so
the answer is that i don't have any tables of column their
datatypes are BYTE
all the columns in my table are of datatype CHAR or VARCHAR

Report message to a moderator

Re: Problem convertion of AL32UTF8 TO AL16UTF16 [message #243973 is a reply to message #243865] Mon, 11 June 2007 02:35 Go to previous messageGo to next message
emadbsb
Messages: 334
Registered: May 2005
Location: egypt
Senior Member
Can i do this
Quote:
ALTER SESSION SET NLS_LENGTH_SEMANTICS = 'CHAR';



And do this will solve the problem

Report message to a moderator

Re: Problem convertion of AL32UTF8 TO AL16UTF16 [message #243977 is a reply to message #243973] Mon, 11 June 2007 02:45 Go to previous messageGo to next message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
This must be done BEFORE the creation of the tables.
This does not change the definition of the already existing table.

I always advice to put this as an instance parameter for a non single byte character set database when creating it to prevent from many futur problems.

Regards
Michel

Report message to a moderator

Re: Problem convertion of AL32UTF8 TO AL16UTF16 [message #243982 is a reply to message #243970] Mon, 11 June 2007 02:59 Go to previous message
Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
CHAR and VARCHAR can be declared with BYTE or CHAR semantic.
VARCHAR2(8 BYTE) means string up to 8 bytes.
VARCHAR2(8 CHAR) means string up to 8 characters.

Default BYTE or CHAR depends on your NLS_LENGTH_SEMANTICS parameter at table creation time.
If NLS_LENGTH_SEMANTICS is not set default is BYTE.

Regards
Michel

Report message to a moderator

Previous Topic: database time
Next Topic: ASM : diskgroup creation error
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Mon Dec 02 12:24:42 CST 2024
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.