How to change Oracle 10gr2 express edition's default character set

Question

I installed oracle 10gr2 express edition on my laptop. when I import a .dmp file which is generated by oracle 10gr2 enterprise edition, error occurs.

the database server which generated the .dmp file is running with GBK charset, but my oracle express server is running with UTF-8.

SQL> select userenv('language') from dual;

USERENV('LANGUAGE')
--------------------------------------------------------------------------------
SIMPLIFIED CHINESE_CHINA.AL32UTF8

how can I configure my own oracle server to import the .dmp file?

edit --------------------------------------------------- my own oracle express server:

SQL> select * from v$nls_parameters where parameter like '%CHARACTERSET';

PARAMETER
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
NLS_CHARACTERSET
AL32UTF8

NLS_NCHAR_CHARACTERSET
AL16UTF16

What error do you get? Why do you believe the error is related to character sets? What is the database and national character set of both databases? — Justin Cave, Feb 27 '12 at 10:06
OK. What is the database and national character set of both databases? `select * from v$nls_parameters where parameter like '%CHARACTERSET'`? — Justin Cave, Feb 27 '12 at 10:16
I can't check character set of the server which generated the dump file right now. — CaiNiaoCoder, Feb 27 '12 at 10:23

score 5 · Answer 1 · answered Feb 27 '12 at 10:54

The new character set requires up to 4 bytes per character while the old one only required up to 2 bytes. So due to the character set change, some character fields will require more space than before. Obviously, some of them have now hit the column lenght limit.

To resolve it, you'll have to increase the length of the affected columns or change the length semantics so the length is interpreted in characters (and not in bytes, which is the default).

If your dump file contains both the schema definition and the data, you'll have to work in phases: first import the schema only, the increase the column lengths and finally import the data.

I have no experience with the length semantics. I usually specify it explicit. See the documentation about the NLS_LENGTH_SEMANTICS parameter for information. It affects how the number 100 in the following statement is interpreted:

CREATE TABLE example (
    id NUMBER,
    name VARCHAR(100)
);

Usually, it's better to be explicit and specify the unit directly:

CREATE TABLE example (
    id NUMBER,
    name VARCHAR(100 CHAR)
);

score 3 · Accepted Answer · answered Feb 28 '12 at 01:32

The dump file contains a whole schema, alter column length is not a good option for me.

the Oracle Express edition use UTF-8 as default, after googled the web, I found a way to alter the database character set.

in my case： UTF-8 --> GBK

I connected with user sys as sysdba in sqlplus. then executed following commands:

shutdown immediate  

startup mount  

alter system enable restricted session ;  

alter system set JOB_QUEUE_PROCESSES=0;  

alter system set AQ_TM_PROCESSES=0;  

alter database open;  

alter database character set internal_use ZHS16GBK ;  

shutdown immediate  

startup

I don't know what these commands done to my database, but It works.

to help others, I hope some oracle experts can explain these commands here. — CaiNiaoCoder, Feb 28 '12 at 01:35

How to change Oracle 10gr2 express edition's default character set

2 Answers2