修改 Oracle XE 的CHARACTERSET


从Oracle 11g上导出一些资料至 oracle xe上时,会出现长度不足的情况。经查发现是NLS_CHARACTERSET设置不同导致的。
Oracle XE安装后,默认为AL32UTF8,这样一个中文字符会占用三个字节,而原来的数据库设置为:ZHS16GBK。以下为将Oracle XE上的AL32UTF8改为ZHS16GBK的方法:

以 sysdba 角色 进入sqlplus 之后执行以下命令:

D:\oraclexe\app\oracle\product\10.2.0\server\BIN\sqlplus.exe / as sysdbaSHUTDOWN 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 ZHS16GBK;
ALTER SESSION SET SQL_TRACE=FALSE;

查看Oracle 參數

select * from v$NLS_PARAMETERS

重启数据库

SHUTDOWN IMMEDIATE;
STARTUP;

修改完成后管理界面会乱码,不用管它。

参考:修改 Oracle XE 的CHARACTERSET

Advertisements

发表评论

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / 更改 )

Twitter picture

You are commenting using your Twitter account. Log Out / 更改 )

Facebook photo

You are commenting using your Facebook account. Log Out / 更改 )

Google+ photo

You are commenting using your Google+ account. Log Out / 更改 )

Connecting to %s