Oracle XE 11.2 Export / Import Charset

Question

I created a database in oracle XE 11.2 that uses charset AL32UTF8 that I now want to export.

When I perform the export, the resulting data appears in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set.

The server uses AL32UTF8 character set (possible charset conversion).

Why is the export from the database in a different charset than the database itself is using?

Answer 1

The export utility will use the NLS_LANG environment variable specified for the client session. If all your data can be represented in the Windows-1252 character set, that shouldn't be an issue. If you want to do the export using the AL32UTF8 character set, you'd need to set the NLS_LANG. In Windows, that would be something like

c:\> set nls_lang = american_america.al32utf8

Answer 2

Steps to migrate the characterset of database

Changing character to WE8MSWIN1252:

Take Full export of database before starting this activity...

#sqlplus system/admin as sysdba

SELECT value$ FROM sys.props$ WHERE name = 'NLS_CHARACTERSET' ;

select * from nls_database_parameters ;

set linesize 2000

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 WE8MSWIN1252;

-- if the above fails:
ALTER DATABASE CHARACTER SET INTERNAL_USE WE8MSWIN1252;

SHUTDOWN IMMEDIATE; — or SHUTDOWN NORMAL;
STARTUP;