oracle imp导入中文乱码

客户给了7个dmp文件共15G左右，需要导入到测试环境，结果导入是成功了，但中文都是乱码。也只怪自己没注意刚开始的提示信息：

 

import done in US7ASCII character set and AL16UTF16 NCHAR character set
import server uses WE8ISO8859P1 character set (possible charset conversion)
export client uses ZHS16GBK character set (possible charset conversion)

 系统有提示编码对不上，查了半天只能重来了,幸好数据库是新安装的，20G的数据量还是比较大，于是先把表空间和用户都删除了干脆一干二净重来。

 

drop tablespace XXXX INCLUDING CONTENTS;
drop user XXXX cascade;

然后到之前存放数据文件的目录把20个数据文件也删除了。

 

附监听命令：

启动监听：lsnrctl start 
查看监听：lsnrctl status 
停止监听：lsnrctl stop 

因安装oracle以及新建实例时未注意字符集，修改数据库字符集相对简单也方便就改数据库字符集了。按照网上：

 

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter system enable restricted session;
SQL> alter system set job_queue_processes=0;
SQL> alter database open;
SQL> alter database character set internal_use ZHS16GBK;
SQL> shutdown immediate;
SQL> startup;

 结果关闭没问题，但不能启动了，报错信息：

 

SQL> startup nomount;
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/home/oracle/oracle/product/10.2.0/db_3/dbs/initorcl.ora'

 把数据库实例对应目录下的复制过去也不行，终于找到解决方法，在运行上面命令前，先把系统环境变量做修改

 

[oracle@localhost table_space]$ export ORACLE_SID=xxxx  xxxx表示你的实例名即sid
[oracle@localhost table_space]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Dec 14 12:29:48 2011

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup mount;
ORACLE instance started.

Total System Global Area  603979776 bytes
Fixed Size                  1220796 bytes
Variable Size             113250116 bytes
Database Buffers          482344960 bytes
Redo Buffers                7163904 bytes
Database mounted.
SQL> 

 

接下来继续剩余 的操作,因为系统是32位的，创建个大表空间还是有些麻烦：

 

create tablespace xxx datafile '/home/oracle/table_space/icms1.dat' SIZE 3G,
'/home/oracle/table_space/icms2.dat' SIZE 3G,'/home/oracle/table_space/icms3.dat' SIZE 3G,
'/home/oracle/table_space/icms4.dat' SIZE 3G,'/home/oracle/table_space/icms5.dat' SIZE 3G,
'/home/oracle/table_space/icms6.dat' SIZE 3G,'/home/oracle/table_space/icms7.dat' SIZE 3G,
'/home/oracle/table_space/icms8.dat' SIZE 3G,'/home/oracle/table_space/icms9.dat' SIZE 3G,
'/home/oracle/table_space/icms10.dat' SIZE 3G,'/home/oracle/table_space/icms11.dat' SIZE 3G,
'/home/oracle/table_space/icms12.dat' SIZE 3G,'/home/oracle/table_space/icms13.dat' SIZE 3G,
'/home/oracle/table_space/icms14.dat' SIZE 3G,'/home/oracle/table_space/icms15.dat' SIZE 3G,
'/home/oracle/table_space/icms16.dat' SIZE 3G,'/home/oracle/table_space/icms17.dat' SIZE 3G,
'/home/oracle/table_space/icms18.dat' SIZE 3G,'/home/oracle/table_space/icms19.dat' SIZE 3G,
'/home/oracle/table_space/icms20.dat' size 3g autoextend on next 1g maxsize unlimited;

 

 

 一次性做了60G的表空间，注意上面表空间的名称最好与dmp文件中涉及到的表空间一致，我之前就有类似提示警告，导致有些脚本不能正常执行。接下来都完成后就是导入数据了：

 

 

imp system/manage@sid file=(/home/20111213DB_DUMP/ic_001.dump,....) log=import.log fromuser=xxxx touser=xxx

最后指定日志文件以观察导入进度和结果。