All Days 무한도전

how to change the character set from WE8MSWIN1252 to AL32UTF8 in oracle rac 11g.

Asian data in AL16UTF16 is more compact than UTF8 and save disk space and have less disk I/O with Asian data. But The maximum lengths for NCHAR and NVARCHAR2 are 1000 and 2000 characters, which is less than the lengths for NCHAR (2000) and NVARCHAR2 (4000) in UTF8.

When we need to use European data... UTF8 better...

Assume I created database with NATIONAL CHARACTER SET AL16UTF16. And need to use UTF8.
Anyway should backup before and Export/[Import] data about NCHAR and NVARCHAR2...
Because We Can not change NATIONAL CHARACTER SET when NCLOB, NCHAR or NVARCHAR2 data exists.

set lines 130
set pages 10
column PROPERTY_NAME format a35
column PROPERTY_VALUE format a35
column DESCRIPTION format a35
select * from database_properties where property_name='NLS_NCHAR_CHARACTERSET'

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
--------------------------------------------------------------
NLS_NCHAR_CHARACTERSET AL16UTF16 NCHAR Character set

create table a (b nvarchar2(4000));
create table a (b nvarchar2(4000))
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype

create table a (b nvarchar2(2001));
create table a (b nvarchar2(2001))
*
ERROR at line 1:
ORA-00910: specified length too long for its datatype

create table a (b nvarchar2(2000));

Table created.

Begin Step by Step...

- SHUTDOWN IMMEDIATE
- STARTUP MOUNT
- ALTER SYSTEM ENABLE RESTRICTED SESSION

Perhaps should:
alter system set job_queue_processes=0
alter system set aq_tm_processes=0

- ALTER DATABASE OPEN
- ALTER DATABASE NATIONAL CHARACTER SET UTF8

If =>
ALTER DATABASE NATIONAL CHARACTER SET UTF8
*
ERROR at line 1:
ORA-12717: Cannot issue ALTER DATABASE NATIONAL CHARACTER SET when NCLOB, NCHAR or NVARCHAR2 data exists

Check alert log and remove data from list (don't forget backup before), Example:

Mon Feb 09 11:52:45 2009
ALTER DATABASE NATIONAL CHARACTER SET UTF8
PM.PRINT_MEDIA (AD_FLTEXTN) - NCLOB populated
ORA-12717 signalled during: ALTER DATABASE NATIONAL CHARACTER SET UTF8....

remark:
ORA-12717: Cannot issue ALTER DATABASE NATIONAL CHARACTER SET when NCLOB, NCHAR or NVARCHAR2 data exists
Cause: NCLOB, NCHAR or NVARCHAR2 data changed the representation to Unicode when converting to a multibyte character set and must be migrated.
Action: Remove NCLOB, NCHAR or NVARCHAR2 data as listed in the alert file. The above type data can be migrated by methods such as import/export.

- SHUTDOWN IMMEDIATE
- STARTUP

PROPERTY_NAME PROPERTY_VALUE DESCRIPTION
---------------------------------------------------------------------
NLS_NCHAR_CHARACTERSET UTF8 NCHAR Character set

create table a (b nvarchar2(4000));

Table created.