No question at this time
DBA Top 10
1 A. Kavsek 12200
2 M. Cadot 7000
3 P. Wisse 5600
4 B. Vroman 5400
5 J. PĂ©ran 1900
6 . Lauri 1500
7 T. P 500
7 R. Pattyn 500
9 T. Boles 400
10 A. Chavan 300
10 J. Schnackenberg 300
10 D. Johnson 300
About
DBA-Village
The DBA-Village forum
Forum as RSS
as RSS feed
Site Statistics
Ever registered users48454
Total active users1499
Act. users last 24h5
Act. users last hour0
Registered user hits last week216
Registered user hits last month950
Go up

Changing Oracle11g 11.2.0.4 database characterset WE8MSWIN1252
Next thread: row to columns
Prev thread: Is there anyway a PGA limitation?

Message Score Author Date
Hi How to change database characterset WE8MSWIN...... BASHYAM RAJA Sep 02, 2019, 11:24
Hi, 1) create new database with AL32UTF8 charac...... Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts Ales Kavsek Sep 02, 2019, 12:29
Hi, of course, you can also try your luck with ...... Ales Kavsek Sep 02, 2019, 12:33
Dear Ales, Thanks, in our database all tables a...... BASHYAM RAJA Sep 02, 2019, 15:16
I will try in out test database.... BASHYAM RAJA Sep 02, 2019, 15:16
Dear Ales, How to find and replace field type v...... BASHYAM RAJA Sep 03, 2019, 11:12
Hi, I didn't say you must replace type VARCHAR2...... Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts Ales Kavsek Sep 03, 2019, 11:41
Thanks a lot Alex.... BASHYAM RAJA Sep 03, 2019, 14:41

Follow up by mail Click here


Subject: Changing Oracle11g 11.2.0.4 database characterset WE8MSWIN1252
Author: BASHYAM RAJA, Bahrain
Date: Sep 02, 2019, 11:24, 78 days ago
Os info: Windows 2012 64 bit
Oracle info: Oracle11g 11.2.0.4
Error info: Changing Oracle11g 11.2.0.4 database characterset WE8MSWIN1252
Message: Hi

How to change database characterset WE8MSWIN1252 to store Arabic and English language characters
The data that is already in the database would need to be converted.

Our present database characterset

OS : Windows 12 R2 64it standard edition
Database :Oracle11g 11.20.4
select * from v$nls_parameters where parameter like '%CHARACTERSET%'

PARAMETER VALUE
-----------------------------------------------
NLS_CHARACTERSET WE8MSWIN1252
NLS_NCHAR_CHARACTERSET AL16UTF16

Best Regards,
B. Raja
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Changing Oracle11g 11.2.0.4 database characterset WE8MSWIN1252
Author: Ales Kavsek, Slovenia
Date: Sep 02, 2019, 12:29, 78 days ago
Score:   Score: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 PtsScore: 300 Pts
Message: Hi,

1) create new database with AL32UTF8 character set
2) export data from 11g (WE8MSWIN1252)
3) import dato to 11g/AL32UTF8 - (why not 19c?)

You must try this on test environment, because everything can get more complicated due to your current CHAR/VARCHAR default semantics, which is (very likely) BYTE and on AL32UTF8 should be CHAR.

If your existing tables are created with:
create table t (name varchar2(4));

and not
create table t (name varchar2(4 char));

then you're (likely) in trouble. If that's the case, then you should either:

- change the sementics on 11g (WE8MSWIN1252) for every CHAR/VARCHAR column in your application schemas before you execute export, something like:

alter table t modify name varchar2(4 char);

- dump DDL for tables, change length semantics, precreate tables on AL32UTF8 and then import data.

I prefer #1.

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Changing Oracle11g 11.2.0.4 database characterset WE8MSWIN1252
Author: Ales Kavsek, Slovenia
Date: Sep 02, 2019, 12:33, 78 days ago
Message: Hi,

of course, you can also try your luck with DMU:

https://www.oracle.com/database/technologies/appdev/migration-assistant-unicode.html

I said, luck, because I was unlucky one :-)...anyway, good luck!

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Changing Oracle11g 11.2.0.4 database characterset WE8MSWIN1252
Author: BASHYAM RAJA, Bahrain
Date: Sep 02, 2019, 15:16, 78 days ago
Message: Dear Ales,

Thanks, in our database all tables are created with varchar2(n) size.


Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Changing Oracle11g 11.2.0.4 database characterset WE8MSWIN1252
Author: BASHYAM RAJA, Bahrain
Date: Sep 02, 2019, 15:16, 78 days ago
Message: I will try in out test database.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Changing Oracle11g 11.2.0.4 database characterset WE8MSWIN1252
Author: BASHYAM RAJA, Bahrain
Date: Sep 03, 2019, 11:12, 77 days ago
Message: Dear Ales,

How to find and replace field type varchar2 into char, is there any easy wayto change field type before importing.

In our database there are 20 schemas and more than 1000 tables.

Any shorcut or alternative method available

Best Regards,
B. Raja
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Changing Oracle11g 11.2.0.4 database characterset WE8MSWIN1252
Author: Ales Kavsek, Slovenia
Date: Sep 03, 2019, 11:41, 77 days ago
Score:   Score: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 PtsScore: 200 Pts
Message: Hi,

I didn't say you must replace type VARCHAR2 to CHAR!
I said you should change length semantics for CHAR/VARCHAR2 fields.

If you create table:

create table t (name varchar2(4));

then if you didn't change parameter NLS_LENGTH_SEMANTICS which is BYTE by default, your actual create statement is:

create table t (name varchar2(4 byte));

Now, if that's the case then you should change existing length semantics from BYTE to CHAR, since you're on 8-bit characterset it doesn't break anything, because it's 1:1.

You can do this with alter statement that I already posted. Of course, you can generate the statements with the information from dictionary, doesn't make a big difference if you have a 20 tables or 20000.

There're no (supported) shortcuts that I'm aware of, so in addition to above method (which I used in a couple of cases) you can also use already mentioned:
- dump DDL, change DDL, run DDL on AL32UTF8, import data (due to dependencies, this can get tricky, I would try to avoid it!)
- try your luck with DMU tool (don't be discouraged by my comment, because Oracle is actively updating the tool, perhaps it's exactly what you're looking for!)

Regards,
Ales
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here

Subject: Re: Changing Oracle11g 11.2.0.4 database characterset WE8MSWIN1252
Author: BASHYAM RAJA, Bahrain
Date: Sep 03, 2019, 14:41, 77 days ago
Message: Thanks a lot Alex.
Your rating?: This reply is Good Excellent
Goto: Reply - Top of page 
If you think this item violates copyrights, please click here