oracle数据字符集和排序-CSDN博客

今天难得有点小时间，实际操作了一下关于oracle数据库中的字符集和排序相关的命令，特此整理出来，以备后用。
SQL> select * from v$nls_valid_values where parameter='CHARACTERSET' order by 2;

PARAMETER                                                        VALUE
---------------------------------------------------------------- -----------------------
CHARACTERSET                                                     AL16UTF16
CHARACTERSET                                                     AL24UTFFSS
CHARACTERSET                                                     AL32UTF8


SQL> select * from nls_database_parameters;

PARAMETER                      VALUE
------------------------------ ---------------------------------------
NLS_LANGUAGE                   AMERICAN--合法的语言名称
NLS_TERRITORY                  AMERICA--合法的地域名称
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               ZHS16GBK--数据库字符集
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR--日期格式
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY--排序方式
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY--和排序相关的东东，具体是什么不清楚
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16--国家字符集
NLS_RDBMS_VERSION              10.1.0.2.0

已选择20行。

已用时间:  00: 00: 00.03

SQL> create table char_t(c1 varchar2(8),c2 nvarchar2(8));

表已创建。

已用时间:  00: 00: 00.21
SQL> select dbms_metadata.get_ddl('TABLE','CHAR_T') FROM DUAL;

DBMS_METADATA.GET_DDL('TABLE','CHAR_T')
--------------------------------------------------------------------------------

  CREATE TABLE "SCOTT"."CHAR_T"
   (    "C1" VARCHAR2(8),
        "C2" NVARCHAR2(8)
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "USERS"


已用时间:  00: 00: 05.15
SQL> INSERT INTO  char_t values('a','a');

已创建 1 行。

已用时间:  00: 00: 00.01
SQL> commit;

提交完成。

已用时间:  00: 00: 00.00
SQL> select length(c1),length(c2),lengthb(c1),lengthb(c2) from char_t;

LENGTH(C1) LENGTH(C2) LENGTHB(C1) LENGTHB(C2)
---------- ---------- ----------- -----------
         1          1           1           2

已用时间:  00: 00: 00.00
SQL> insert into char_t values('中','中');

已创建 1 行。

已用时间:  00: 00: 00.00
SQL>  select length(c1),length(c2),lengthb(c1),lengthb(c2) from char_t;

LENGTH(C1) LENGTH(C2) LENGTHB(C1) LENGTHB(C2)
---------- ---------- ----------- -----------
         1          1           1           2
         1          1           2           2

已用时间:  00: 00: 00.00
SQL>  insert into char_t values('遽','遽');

已创建 1 行。

已用时间:  00: 00: 00.01
SQL> select length(c1),length(c2),lengthb(c1),lengthb(c2) from char_t;

LENGTH(C1) LENGTH(C2) LENGTHB(C1) LENGTHB(C2)
---------- ---------- ----------- -----------
         1          1           1           2
         1          1           2           2
         1          1           2           2

已用时间:  00: 00: 00.01

至于一个VARCHAR2字符要用几个字节存储，要看数据库使用的字符集，
比如GBK，汉字就会占两个字节，英文1个，如果是UTF-8，汉字一般占3个字节，英文还是1个。 

而NCHAR、NVARCHAR是以UNICODE-16存储，每个字符固定两个字节。

SQL> select hiredate from emp;

HIREDATE
------------
17-DEC-80
20-FEB-81
22-FEB-81
02-APR-81
28-SEP-81


SQL> show user;
USER is "SCOTT"
SQL> alter session set nls_language='simplified chinese'

会话已更改。

已用时间:  00: 00: 00.01
SQL> select hiredate from emp;

HIREDATE
--------------
17-12月-80
20-2月 -81
22-2月 -81
02-4月 -81

已用时间:  00: 00: 00.01

查看session中支持的语言名称
 1* select * from v$nls_valid_values where parameter='LANGUAGE' order by 2
QL> /

ARAMETER                                                        VALUE
--------------------------------------------------------------- ---------------------------
ANGUAGE                                                         AMERICAN
ANGUAGE                                                         ARABIC
ANGUAGE                                                         ASSAMESE
ANGUAGE                                                         AZERBAIJANI
ANGUAGE                                                         BANGLA
ANGUAGE                                                         BENGALI
ANGUAGE                                                         BRAZILIAN PORTUGUESE
查看合法的地域名称
SQL> select * from v$nls_valid_values where parameter='TERRITORY' order by 2;

PARAMETER                                                        VALUE
---------------------------------------------------------------- --------------------------
TERRITORY                                                        ALGERIA
TERRITORY                                                        AMERICA
TERRITORY                                                        AUSTRIA
TERRITORY                                                        AZERBAIJAN
TERRITORY                                                        CANADA
TERRITORY                                                        CATALONIA
TERRITORY                                                        CHILE
TERRITORY                                                        CHINA


查看合法的排行名称
SQL> select * from v$nls_valid_values where parameter='SORT' order by 2;

PARAMETER                                                        VALUE
---------------------------------------------------------------- -----------------------
SORT                                                             SCHINESE_PINYIN_M
SORT                                                             SCHINESE_RADICAL_M
SORT                                                             SCHINESE_STROKE_M

查看当前会话的排行模式(系统默认的排行方式是：BINARY)：
SQL> select value from nls_session_parameters where parameter='NLS_SORT';

VALUE
--------------------------------------------------------------------------------
BINARY

修改排行方式的测试：
SQL> alter session set nls_sort='SCHINESE_PINYIN_M';

会话已更改。

已用时间:  00: 00: 00.00
SQL> select * from sort_tab order by 1;

C
----------
啊
藏--这个是个多音字,在此处oracle把他当cang了。
木
目
人
三
一

已选择7行。

已用时间:  00: 00: 00.01

SQL>  alter session set NLS_SORT='SCHINESE_STROKE_M';--按照笔划（第一顺序）、部首（第二顺序）排序

会话已更改。

已用时间:  00: 00: 00.09
SQL>  select * from sort_tab order by 1;

C
----------
一
人
三
木
目
啊
藏

已选择7行。

已用时间:  00: 00: 00.00
SQL> alter session set NLS_SORT='GBK';

会话已更改。

已用时间:  00: 00: 00.01
SQL> select * from sort_tab order by 1;

C
----------
啊
藏
木
目
人
三
一

已选择7行。

已用时间:  00: 00: 00.00
SQL> alter session set NLS_SORT='SCHINESE_RADICAL_M';--按照部首（第一顺序）、笔划（第二顺序）排序

会话已更改。

已用时间:  00: 00: 00.04
SQL>  select * from sort_tab order by 1;

C
----------
一
三
人
啊
木
目
藏

已选择7行。

已用时间:  00: 00: 00.01
SQL> ALTER SESSION SET NLS_SORT=BINARY;--按字符的二进制值比较

会话已更改。

已用时间:  00: 00: 00.00
SQL> select * from sort_tab order by 1;

C
----------
啊
藏
木
目
人
三
一

已选择7行。

已用时间:  00: 00: 00.01

注意：如果NLS_SORT不是设置为"Binary"，那么就会引起全表扫描，
是不会使用索引的，在我们的系统中变更单涉及到的数据都是数据庞大的表，
如果不使用到索引，查询的效率不受到影响才怪呢！ 

修改数据库字符集：
alter database "orcl" character set ZHS16CGB231280;
修改国家语言字符集：
alter database "orcl" national character set ZHS16CGB231280;