ITPub博客

首页 > Linux操作系统 > Linux操作系统 > oracle中文乱码问题处理

oracle中文乱码问题处理

原创 Linux操作系统 作者:liu_qingwei2008 时间:2011-11-17 17:48:26 0 删除 编辑
环境描述:
测试库: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:
oracle@sale-server:~> 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
 

生产库导入:
oracle@sale-server:~/> mkdir exp
oracle@sale-server:~/> 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.
 

oracle@sale-standby:~/> export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
oracle@sale-standby:~/> echo $NLS_LANG
AMERICAN_AMERICA.AL32UTF8

oracle@sale-standby:~/exp> impdp system/oracle directory=exp dumpfile=jb_1.dmp logfile=jb_1_imp.log schemas=jb_1
oracle@sale-standby:~/exp> impdp system/oracle directory=exp dumpfile=jb.dmp logfile=jbimp.log schemas=jb

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/23898243/viewspace-711478/,如需转载,请注明出处,否则将追究法律责任。

下一篇: ORA-01652错误解决
请登录后发表评论 登录
全部评论

注册时间:2010-05-12

  • 博文量
    37
  • 访问量
    86582