oracle过率乱码,oracle中文乱码问题处理

环境描述：

测试库：win2008 11.2.0.1.0 64bit 字符集AL32UTF8

生产库：suse10  11.2.0.1.0 64bit 字符集WE8MSWIN1252

检查字符集：

windows2008:

Microsoft Windows [版本 6.0.6001]

版权所有 (C) 2006 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 2011

Copyright (c) 1982, 2010, 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 2011

Copyright (c) 1982, 2010, 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 2011

Copyright (c) 1982, 2009, 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 2011

Copyright (c) 1982, 2009, 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