Search code examples
oracle10gora-12899

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


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

Solution

  • 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.