环境描述：
测试库：win 11.2.0.1.0 64bit 字符集AL32UTF8
生产库：suse10 11.2.0.1.0 64bit 字符集WE8MSWIN1252
检查字符集：
windows:
Microsoft Windows [版本 6.0.6001]
版权所有 (C) Microsoft Corporation。保留所有权利。
C:\Users\Administrator>chcp
活动代码页: 936
C:\Users\Administrator>cd c:\
c:\>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期一 11月 14 16:01:48
Copyright (c) 1982, , Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ --------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET AL32UTF8
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
PARAMETER VALUE
------------------------------ --------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXF
F AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXF
F AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
PARAMETER VALUE
------------------------------ --------------------
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.2.0.1.0
已选择20行。
c:\>sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on 星期二 11月 15 13:30:05
Copyright (c) 1982, , Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
--------------------------------------------------------------------------------
SIMPLIFIED CHINESE_CHINA.AL32UTF8
linux:
:~> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 14 14:25:59
Copyright (c) 1982, , Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select * from nls_database_parameters;
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY $
NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,
NLS_CHARACTERSET WE8MSWIN1252
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT DD-MON-RR
NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY $
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
NLS_NCHAR_CHARACTERSET AL16UTF16
NLS_RDBMS_VERSION 11.2.0.1.0
20 rows selected.
SQL> select userenv('language') from dual;
USERENV('LANGUAGE')
----------------------------------------------------
AMERICAN_AMERICA.WE8MSWIN1252
实现目标：将测试库jb和jb_1用户下的数据导入到生产库(exp/imp)
生产库创建用户及表空间：
create tablespace JBCRM datafile '/u01/app/oracle/oradata/JBCRM.dbf' size 5000m;
create user jb identified by jb default tablespace JBCRM;
create user jb_1 identified by jb_1 default tablespace JBCRM;
grant connect,resource to jb,jb_1;
grant dba to jb,jb_1;
alter user jb quota 0 on JBCRM;
alter user jb_1 quota 0 on JBCRM;
alter user jb quota unlimited on JBCRM;
alter user jb_1 quota unlimited on JBCRM;
故障现象：导入后的中文数据是乱码
生产库处理过程：
shutdown immediate;
STARTUP MOUNT;
ALTER SESSION SET SQL_TRACE=TRUE;
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 AL32UTF8;
ALTER SESSION SET SQL_TRACE=FALSE;
shutdown immediate;
startup;
SELECT parameter, value FROM v$nls_parameters WHERE parameter LIKE '%CHARACTERSET';
SQL> SELECT parameter, value FROM v$nls_parameters WHERE parameter LIKE '%CHARACTERSET';
PARAMETER
----------------------------------------------------------------
VALUE
----------------------------------------------------------------
NLS_CHARACTERSET
AL32UTF8
NLS_NCHAR_CHARACTERSET
AL16UTF16
测试库导出：
SQL> create directory exp as 'c:\';
Directory created.
SQL> grant all on directory exp to public;
Grant succeeded.
c:\>set NLS_LANG=SIMPLIFIED CHINESE_CHINA.AL32UTF8
c:\>echo %NLS_LANG%
c:\>expdp system/oracle directory=exp dumpfile=jb.dmp logfile=jb.log schemas=jb
c:\>expdp system/oracle directory=exp dumpfile=jb_1.dmp logfile=jb_1.log schemas=jb_1
生产库导入：
> mkdir exp
> sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Nov 14 15:29:01
Copyright (c) 1982, , Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create directory exp as '/home/oracle/exp';
Directory created.
SQL> grant all on directory exp to public;
Grant succeeded.
> export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
> echo $NLS_LANG
AMERICAN_AMERICA.AL32UTF8
> impdp system/oracle directory=exp dumpfile=jb_1.dmp logfile=jb_1_imp.log schemas=jb_1
> impdp system/oracle directory=exp dumpfile=jb.dmp logfile=jbimp.log schemas=jb
