你的位置:首页 > 数据库

[数据库]sqlldr 采集中文数据乱码问题


测试机上装入数据 发现中文字段全部变成???????,初步判断为字符集问题

   更改
     UPDATE sys.props$
     SET VALUE$='WE8ISO8859P1'
     where name like 'NLS%'
     and value$='ZHS16GBK';
commit;
后发现sqlldr采集数据时,报错
SQL*Loader-704: Internal error: ulmtsyn: OCIStmtExecute(tabhp) [6552]
ORA-06552: PL/SQL: Compilation unit analysis terminated
ORA-06553: PLS-553: character set name is not recognized

 
查资料后发现原来更改字符集还需要在设置字符集时把数据库设置成RESTRICTED模式等诸多操作,进行以下操作后,sqlldr采集正常
>STARTUP MOUNT;
 ORACLE instance started. 
 Total System Global Area 76619308 bytes
 Fixed Size 454188 bytes 
 Variable Size 58720256 bytes
 Database Buffers 16777216 bytes 
 Redo Buffers 667648 bytes 
Database mounted. 
SQL> ALTER SESSION SET SQL_TRACE=TRUE;
 Session altered. 
 SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;
 System altered. 
 SQL> ALTER SYSTEM SET JOB_QUEUE_PROCESSES=0; 
 System altered.
 SQL> ALTER SYSTEM SET AQ_TM_PROCESSES=0; 
 System altered.
 SQL> ALTER DATABASE OPEN;
 Database altered. 
 SQL> set linesize 120 
SQL> ALTER DATABASE CHARACTER SET ZHS16GBK;
ORA-12712: new character set must be a superset of old character setRROR at line 1:

>ALTER DATABASE character set INTERNAL_USE zhs16gbk;

>ALTER SESSION SET SQL_TRACE=FALSE;