修改oracle数据库的字符编码

本文介绍如何解决Oracle10g数据库建库时未选择正确字符集导致的数据乱码问题,通过具体步骤演示如何将数据库字符集从WE8ISO8859P1修改为ZHS16GBK。

摘要生成于 C知道 ,由 DeepSeek-R1 满血版支持, 前往体验 >

最近安装个Oracle 10g数据库,dbca建库的时候没选字符集或者选错的情况,导入数据后,中文全部是乱码或者不能正常导入新的库,出现表字段长度不够的错误,解决办法如下:
SQL> connect / as sysdba; ---dba用户连接数据库

SQL> select name,value$ from props$ where name like '%NLS%';---语句查询当前数据库服务器字符集

NAME
------------------------------------------------------------
VALUE$
--------------------------------------------------------------------------------
NLS_LANGUAGE
AMERICAN

NLS_TERRITORY
AMERICA

NLS_CURRENCY
$


NAME
------------------------------------------------------------
VALUE$
--------------------------------------------------------------------------------
NLS_ISO_CURRENCY
AMERICA

NLS_NUMERIC_CHARACTERS
.,

NLS_CHARACTERSET ----找到查出来的结果中的该项参数
WE8ISO8859P1



下面是修改过程:

SQL> shutdown immediate; ---停止数据库
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount ---启动数据库到mount状态
ORACLE instance started.

Total System Global Area 235999352 bytes
Fixed Size 450680 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 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;
alter database character set zhs16gbk
*
ERROR at line 1:
ORA-12712: new character set must be a superset of old character set

SQL> ALTER DATABASE character set INTERNAL_USE zhs16gbk;

# 使用INTERNAL_USE可以跳过超集的检查,

(ALTER DATABASE character set INTERNAL_USE )

Database altered.
SQL> shutdown immediate; ---再停止数据库
Database closed.
Database dismounted.
ORACLE instance shut down.

---启动数据库
SQL> STARTUP
ORACLE instance started.

Total System Global Area 235999352 bytes
Fixed Size 450680 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>



----再次查看当前字符集

SQL> select name,value$ from props$ where name like '%NLS%';

NAME
------------------------------
VALUE$
--------------------------------------------------------------------------------
NLS_LANGUAGE
SIMPLIFIED CHINESE

NLS_TERRITORY
CHINA

NLS_CURRENCY
?


NAME
------------------------------
VALUE$
--------------------------------------------------------------------------------
NLS_ISO_CURRENCY
CHINA

NLS_NUMERIC_CHARACTERS
.,

NLS_CHARACTERSET
ZHS16GBK ---【已经修改成为中文编码了】


原文链接:http://www.linuxidc.com/Linux/2011-07/38114.htm
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值