Oracle-30-Oracle全球化支持及字符集

如果数据库中需要支持多语言（最简单的就是英文和中文），那么就要对数据库做一些设置：比如使用Unicode、设置NLS相关参数等，设置分为Client端和Server端，Client端通过设置环境变量对指定语言进行支持，Server端通过修改NLS参数来实现对指定语言的支持。

oracle数据库中使用字符集的优先级：SQL函数中的设置>ALTER SESSION语句设置>客户端的环境变量>初始化参数文件中指定>默认字符集

我们先修改Server端字符集

首先查看数据库字符集

SYS@ orcl>col parameter for a20
SYS@ orcl>col value for a30
SYS@ orcl>SELECT * FROM NLS_DATABASE_PARAMETERS where parameter='NLS_CHARACTERSET';

PARAMETER            VALUE
-------------------- ------------------------------
NLS_CHARACTERSET     WE8MSWIN1252

修改数据库字符集，让它可以支持中文

SYS@ orcl> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SYS@ orcl> startup mount;
ORACLE instance started.

Total System Global Area  784998400 bytes
Fixed Size                  2217464 bytes
Variable Size             469764616 bytes
Database Buffers          310378496 bytes
Redo Buffers                2637824 bytes
Database mounted.

SYS@ orcl>alter session set sql_trace=true;

Session altered.

Elapsed: 00:00:00.00

SYS@ orcl> alter system enable restricted session;

System altered.

Elapsed: 00:00:02.02

SYS@ orcl> alter system set job_queue_processes=0;

System altered.

Elapsed: 00:00:00.04

SYS@ orcl> alter system set aq_tm_processes=0;

System altered.

Elapsed: 00:00:00.02

SYS@ orcl> alter database open;

Database altered.

Elapsed: 00:00:01.50

SYS@ orcl> alter database character set INTERNAL_USE AL32UTF8;

Database altered.

Elapsed: 00:00:08.33

查看更改后结果

SYS@ orcl> select * from nls_database_parameters where parameter = 'NLS_CHARACTERSET';

PARAMETER            VALUE
-------------------- ------------------------------
NLS_CHARACTERSET     AL32UTF8

Elapsed: 00:00:00.02

接下来我们修改Client端字符集

[oracle@RHEL6 ~]$ su - oracle
Password: 

[oracle@RHEL6 ~]$ vi  .bashrc

# .bashrc

# Source global definitions
if [ -f /etc/bashrc ]; then
        . /etc/bashrc
fi

# User specific aliases and functions
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1
export ORACLE_OWNER=oracle
export ORACLE_SID=orcl
export ORACLE_TERM=vt100
export THREADS_FLAG=native
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export PATH=$ORACLE_HOME/bin:$PATH
export EDITOR=vim
export SQLPATH=/home/oracle
export LANG=en_us.uft8
export NLS_LANG="AMERICAN_AMERICA.AL32UTF8"

alias sqlplus='rlwrap sqlplus'
alias lsnrctl='rlwrap lsnrctl'
alias rman='rlwrap rman'
alias asmcmd='rlwrap asmcmd'

~
~
".bashrc" 26L, 637C written  

修改里面的NLS_LANG参数，保存退出

[oracle@RHEL6 ~]$ source .bashrc

我们重新登录sqlplus查看效果

[oracle@RHEL6 ~]$ sqlplus oracle/oracle

SQL*Plus: Release 11.2.0.1.0 Production on Mon Jul 23 10:45:35 2018

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

ORACLE@ orcl>select * from test;

A
--------------------
好
123

Elapsed: 00:00:00.00

这样我们就修改完成了