| Changing CHARACTER SET [message #599882] |
Tue, 29 October 2013 15:57  |
jxh461
Messages: 185
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 #599971 is a reply to message #599900] |
Wed, 30 October 2013 10:14  |
jxh461
Messages: 185
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 #600806 is a reply to message #599885] |
Tue, 12 November 2013 12:34 |
jxh461
Messages: 185
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 #600809 is a reply to message #600807] |
Tue, 12 November 2013 14:01 |
jxh461
Messages: 185
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.
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
