Home » RDBMS Server » Server Administration » Changing CHARACTER SET (Oracle 11.2 on Windows server 2008)
Changing CHARACTER SET [message #599882] Tue, 29 October 2013 15:57 Go to next message
jxh461
Messages: 182
Registered: March 2005
Senior Member
Hi All,

I am required to pump some data into a new database. The source database has the following settings:

SQL> SELECT * FROM NLS_DATABASE_PARAMETERS;

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               WE8MSWIN1252
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.1.0.7.0



The new database that I just created has the following settings:

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               AL32UTF8
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.2.0.1.0

20 rows selected.


Note the NLS_CHARACTER settings are different. I want them to match in anticipation of a smooth data migration.
I tried to alter:
SQL> select parameter,value from nls_database_parameters
  2  where parameter in ('NLS_CHARACTERSET','NLS_NCHAR_CHARACTERSET');

PARAMETER                      VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET               AL32UTF8
NLS_NCHAR_CHARACTERSET         AL16UTF16

SQL> ALTER DATABASE CHARACTER SET WE8MSWIN1252;
ALTER DATABASE CHARACTER SET WE8MSWIN1252
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set



I have read some documentation and I came across an option to use "INTERNAL_USE" in my command but I
am not confortable doing this. Please advise on what to do if I need to do anything at all.

Thanks in advance for your contribution.
Re: Changing CHARACTER SET [message #599885 is a reply to message #599882] Tue, 29 October 2013 17:03 Go to previous messageGo to next message
BlackSwan
Messages: 23157
Registered: January 2009
Senior Member
>Please advise on what to do if I need to do anything at all.
The characterset of any Oracle database is established when the database is created.
You should not have any problem since UTF8 is a superset of WE8MSWIN1252
Re: Changing CHARACTER SET [message #599900 is a reply to message #599882] Wed, 30 October 2013 02:33 Go to previous messageGo to next message
Michel Cadot
Messages: 60010
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
I have read some documentation and I came across an option to use "INTERNAL_USE" in my command


This is wrong, INTERNAL_USE is not in the documentation, it is a hidden option only for... Oracle internal use.

The only thing you have to do in your new database is to change NLS_LENGTH_SEMANTICS to CHAR.
And the only thing you have to do in your old database is to change the definition of all your VARCHAR2(N BYTE) to VARCHAR2(N CHAR).

Re: Changing CHARACTER SET [message #599971 is a reply to message #599900] Wed, 30 October 2013 10:14 Go to previous messageGo to next message
jxh461
Messages: 182
Registered: March 2005
Senior Member
Quote:
This is wrong, INTERNAL_USE is not in the documentation, it is a hidden option only for... Oracle internal use.



I should have been more acurate. I came across this information not in the documentation but rather in other posts.
Re: Changing CHARACTER SET [message #599975 is a reply to message #599971] Wed, 30 October 2013 10:48 Go to previous messageGo to next message
Michel Cadot
Messages: 60010
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
jxh461 wrote on Wed, 30 October 2013 16:14
Quote:
This is wrong, INTERNAL_USE is not in the documentation, it is a hidden option only for... Oracle internal use.



I should have been more acurate. I came across this information not in the documentation but rather in other posts.


Anyway, forget it.

Re: Changing CHARACTER SET [message #600806 is a reply to message #599885] Tue, 12 November 2013 12:34 Go to previous messageGo to next message
jxh461
Messages: 182
Registered: March 2005
Senior Member
Quote:
You should not have any problem since UTF8 is a superset of WE8MSWIN1252


Isn't the following error message a result of this ? I am getting this while importing.

ORA-02374: conversion error loading table "OWNERW"."TABLE_NAME_DIM"
ORA-12899: value too large for column TABLE_COLUMN_W (actual: 26, maximum: 16)

ORA-02372: data for row: TABLE_COLUMN_W : 0X'CE037488370F3E02822FC4D010928188'


Please advise on what I can do to in my impdp command to fix this during the import.

[Updated on: Tue, 12 November 2013 12:34]

Report message to a moderator

Re: Changing CHARACTER SET [message #600807 is a reply to message #600806] Tue, 12 November 2013 12:37 Go to previous messageGo to next message
BlackSwan
Messages: 23157
Registered: January 2009
Senior Member
POST RESULTS FROM DESC "OWNERW"."TABLE_NAME_DIM" against both source and target databases
Re: Changing CHARACTER SET [message #600809 is a reply to message #600807] Tue, 12 November 2013 14:01 Go to previous messageGo to next message
jxh461
Messages: 182
Registered: March 2005
Senior Member
Quote:

You should not have any problem since UTF8 is a superset of WE8MSWIN1252


Whether you are right or wrong about the statement above, Oracle does not seem to think so when
I did ALTER DATABASE statement to change the NLS_CHARACTERSET.

I ended up changing the column length.
Re: Changing CHARACTER SET [message #600815 is a reply to message #600809] Tue, 12 November 2013 15:18 Go to previous message
BlackSwan
Messages: 23157
Registered: January 2009
Senior Member
why were column different lengths to start with?
Previous Topic: Database migration
Next Topic: Oracle Client 11.2.0.3 not working
Goto Forum:
  


Current Time: Sun Dec 21 05:43:55 CST 2014

Total time taken to generate the page: 0.11168 seconds