select * from nls_database_parameters; …. ………….. NLS_CHARACTERSET AL32UTF8 ….. NLS_LENGTH_SEMANTICS BYTE …. NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_RDBMS_VERSION 10.2.0.4.0 |
SQL> SQL> create table nls_byte(c1 varchar2(7)); SQL> insert into nls_byte values('测试机');
insert into nls_byte values('测试机') ORA-12899: 列 "SYS"."NLS_BYTE"."C1" 的值太大 (实际值: 9, 最大值: 7) SQL> insert into nls_byte values('测试a'); 1 row inserted |
SQL> select table_name,column_name,t.DATA_TYPE,t.DATA_LENGTH,t.CHAR_USED from user_tab_columns t where table_name='NLS_BYTE';
TABLE_NAME COLU DATA_TYP DATA_LENGTH CHAR_USED ---------- ---- -------- ----------- --------- NLS_BYTE C1 VARCHAR2 7 B |
NLS_LENGTH_SEMANTICS allows you to specify the length of a column datatype in terms of CHARacters rather than in terms of BYTEs. Typically this is when using an AL32UTF8 or other varying width NLS_CHARACTERSET database where one character is not always one byte. While using CHAR semantics has as such no added value in a 7/8 bit characterset it's fully supported so any application code / table setup using CHAR can also be used in a 7/8bit characterset like US7ASCII/WE8MSWIN1252.
This parameter is a 9i (and up) feature and is not available in older releases
翻译过来就是:这个参数允许将列的数据单位设为字符而不是byte.这个问题会在字符集设为UTF8的时候出现. 此参数在9i以上版本有效.
NLS_LENGTH_SEMANTICS 设置.
1. NLS_DATABASE_PARAMETERS中的值是在数据库创建的时候确定的,一般都为BYTE
2. 此参数可以以 “ALTER SYSTEM SET NLS_LENGTH_SEMANTICS=CHAR scope=both”方式修改,但是需要重启数据库才能生效.
3. 也可用” ALTER SESSION SET NLS_LENGTH_SEMANTICS=CHAR”使对当前session生效.
4. 此参数可以在10G以上版本中,在环境变量或注册表中设置(注意需要大写),设定后从当前客户端启动的所有会话都采用新的取值.
5. 修改后只对新建的列生效,对于已有的列没有作用
6. 新建或升级DB时用BYTE,否则XDB或dba_tables会出现问题.
7. NLS_LENGTH_SEMANTICS对sys用户下的对象无效.
8. 如果对于7/8bit的字符集,设为byte/char意义不大,因为无论是char和byte都对应一个byte.
测试:
一.在当前session中修改此参数
SQL> alter session set nls_length_semantics='char';
Session altered
SQL> create table nls_char(c1 varchar2(7),c2 varchar2(7));
Table created
SQL> desc nls_char Name Type Nullable Default Comments ---- ----------- -------- ------- -------- C1 VARCHAR2(7) Y C2 VARCHAR2(7) Y
SQL> insert into nls_char values('测试机','测试测试测试');
1 row inserted |
如果对于alter system,效果是一样的
二.对于已经存在的表,
SQL> desc nls_byte Name Type Nullable Default Comments ---- ---------------- -------- ------- -------- C1 VARCHAR2(7 BYTE) Y SQL> alter table nls_byte modify c1 varchar2(7 char); Table altered
SQL> desc nls_byte Name Type Nullable Default Comments ---- ----------- -------- ------- -------- C1 VARCHAR2(7) Y
SQL> insert into nls_byte values('测试机');
1 row inserted |
1. exp/imp : 不能直接导入,因为会采用source table的建表方式在target db里建表,即使目标库设的值为char.
*可以预先在目标库中以char方式建表
*然后导入,指定参数ignore=y
2. Alter table
alter table ""."
" modify "" char (10 char);
创建脚本
,
修改列设定
.
注
:
Bug-3611750, ora-01450 online rebuild of index fails,
可以在重建索引前指定
byte, 10.2.0.5
以上已经修复
Bug 1488174 UNICODE: ALTER SYSTEM SET NLS_LENGTH_SEMANTICS DOESN'T
TAKE EFFECT, 用此语句修改后,实际上不起作用,需要重启才能生效, 但是如果用alter session方式即时生效,不用重启.
进一步测试,在另一个字符集设为us7ascii的DB设置此参数
SQL> select * from nls_database_parameters
6 NLS_CHARACTERSET US7ASCII
SQL> alter session set nls_length_semantics=byte;
Session altered.
SQL> create table nls_byte(c1 varchar2(7));
Table created.
SQL> insert into nls_byte values('测试测试');
insert into nls_byte values('测试测试')
ERROR at line 1:
ORA-12899: value too large for column "TEA"."NLS_BYTE"."C1" (actual: 8,
maximum: 7)
SQL> desc nls_byte
Name Null? Type
-------------------------------
C1 VARCHAR2(7)
SQL> alter session set nls_length_semantics=char;
Session altered.
SQL> create table nls_char(c1 varchar2(7));
Table created.
SQL> insert into nls_char values('测试测试');
insert into nls_char values('测试测试')
ERROR at line 1:
ORA-12899: value too large for column "TEA"."NLS_CHAR"."C1" (actual: 8,
maximum: 7)
SQL> desc nls_char
Name Null? Type
----------------------------------------- -------- -------------------
C1 VARCHAR2(7)
可以看出,在字符集为单字节的情况下,无论取何值,汉字都是以二个字节的方式存在的.
[@more@]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/256370/viewspace-1025274/,如需转载,请注明出处,否则将追究法律责任。
<%=items[i].content%>
<%if(items[i].items.items.length) { %>
<%for(var j=0;j
<%=items[i].items.items[j].createtime%>
<%=items[i].items.items[j].username%> 回复 <%=items[i].items.items[j].tousername%>: <%=items[i].items.items[j].content%>
<%}%> <%if(items[i].items.total > 5) { %>
还有<%=items[i].items.total-5%>条评论) data-count=1 data-flag=true>点击查看
<%}%>
<%}%> <%}%>
最新文章
MES产品选择评价指标(1) 通过生产管理流程进行MES选型 MES选型指导 MES产品选择评价指标(3) AIX 下 expdp 的exclude Oracle 层次化查询 十二经络运行时间及养生 Segment Advisor脚本 rowID explaination Oracle Hint
转载于:http://blog.itpub.net/256370/viewspace-1025274/
全部评论
<%=items[i].createtime%>