oracle 更改字元集

事件環境：

伺服器A字元集和伺服器B字元集不一致.

伺服器A exp命令匯出 dmp檔案後,在伺服器B上 imp匯入中文出現亂碼.

 解決問題思路:更改伺服器B上面oracle 服務的字元集(實質等同於伺服器A oracle的字元集).

1 查看伺服器A字元集

sql>conn sys as sysdbasql>select * from v$nls_parameters where parameter='NLS_CHARACTERSET';

   2. 刪除伺服器B上面已經存在的使用者資料表空間,並建立資料表空間使用者

 drop user IDHR cascade; drop tablespace IDHRDEVDB;

--初始化資料庫資訊create tablespace  IDHRDEVDBloggingdatafile 'D:\oraclexe\app\oracle\oradata\IDHRDEVDB\IDHRDEVDB.DBF'size 32mautoextend on extent management local;CREATE USER IDHR IDENTIFIED BY IDHR _0525PROFILE DEFAULTDEFAULT TABLESPACE IDHRDEVDBACCOUNT UNLOCK;grant all privileges to IDHR;  grant   sysdba   to   IDHR;

 

 3.修改伺服器B的oracle字元集

http://blog.csdn.net/lyn_bigdream/article/details/7473278

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;

 

4 再次在伺服器B上面匯入dmp資料

imp IDHR/IDHR_0525 file=F:\AIT\Lottes\DB\初始化資料庫語句\Database\ss_hr.dmp  log=F:\AIT\Lottes\DB\初始化資料庫語句\Database\ss_hr.log  fromuser=indonesiaprod2

5 遠端連線伺服器B 資料庫,中文顯示正常.