字符集问题的初步探讨(三)

link:

http://www.eygle.com/special/NLS_CHARACTER_SET_03.htm

2. 字符集的更改

数据库创建以后,如果需要修改字符集,通常需要重建数据库,通过导入导出的方式来转换。 我们也可以通过以下方式更改

ALTER DATABASE CHARACTER SET

注意:修改数据库字符集时必须谨慎,修改之前一定要为数据库备份。由于不能回退这项操作,因此可能会造成数据丢失或者损坏。 这是最简单的转换字符集的方式,但并不总是有效。 这个命令在Oracle8时被引入Oracle,这个操作在本质上并不转换任何数据库字符,只是简单的更新数据库中所有跟字符集相关的信息。

这意味着,你只能在新字符集是旧字符集严格超集的情况下使用这种方式转换。 所谓超集是指: 当前字符集中的每一个字符在新字符集中都可以表示,并使用同样的代码点 比如很多字符集都是US7ASCII的严格超集。

如果不是超集,将获得以下错误:

下面我们来看一个测试(以下测试在Oracle9.2.0下进行,Oracle9i较Oracle8i在编码方面有较大改变,在Oracle8i中,测试结果可能略有不同):

SQL> select name,value$ from props$ where name like '%NLS%'; NAME VALUE$ ------------------------------ ------------------------------ NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET US7ASCII NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN ………………. NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_RDBMS_VERSION 9.2.0.4.0 20 rows selected. SQL> select name,dump(name) from eygle.test; NAME DUMP(NAME) ------------------------------------------------------ 测试 Typ=1 Len=4: 178,226,202,212 Test Typ=1 Len=4: 116,101,115,116 2 rows selected.

转换字符集,数据库应该在RESTRICTED模式下进行.

c:/>sqlplus "/ as sysdba" SQL*Plus: Release 9.2.0.4.0 - Production on Sat Nov 1 10:52:30 2003 Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved. Connected to: Oracle9i Enterprise Edition Release 9.2.0.4.0 - Production With the Partitioning, Oracle Label Security, OLAP and Oracle Data Mining options JServer Release 9.2.0.4.0 - Production SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> STARTUP MOUNT; ORACLE instance started. Total System Global Area 76619308 bytes Fixed Size 454188 bytes Variable Size 58720256 bytes Database Buffers 16777216 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-12721: operation cannot execute when other sessions are active SQL> ALTER DATABASE CHARACTER SET ZHS16GBK; ALTER DATABASE CHARACTER SET ZHS16GBK * ERROR at line 1: ORA-12716: Cannot ALTER DATABASE CHARACTER SET when CLOB data exists 在Oracle9i中,如果数据库存在CLOB类型字段,那么就不允许对字符集进行转换 SQL>

这时候,我们可以去查看alert<sid>.log日志文件,看CLOB字段存在于哪些表上:

对于不同情况,Oracle提供不同的解决方案,如果是用户数据表,一般我们可以把包含CLOB字段的表导出,然后drop掉相关对象, 转换后再导入数据库;对于系统表,可以按照以下方式处理:

Table truncated.

然后可以继续进行转换!

SQL> ALTER SESSION SET SQL_TRACE=TRUE; Session altered. SQL> ALTER DATABASE CHARACTER SET ZHS16GBK; Database altered. SQL> ALTER SESSION SET SQL_TRACE=FALSE; Session altered.

在9.2.0中,转换完成以后,可以通过运行catmet.sql脚本来重建Metastylesheet表:

SQL> @?/rdbms/admin/catmet.sql

转换后的数据:

SQL> select name,value$ from props$ where name like '%NLS%'; NAME VALUE$ ------------------------------ ------------------------------ NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET ZHS16GBK ….. NLS_NCHAR_CHARACTERSET AL16UTF16 NLS_RDBMS_VERSION 9.2.0.4.0 20 rows selected. SQL> select * from eygle.test; NAME ------------------------------ 测试 test 2 rows selected.

提示: 通过设置sql_trace,我们可以跟踪很多数据库的后台操作,这个工具是DBA常用的“利器”之一。 我们简单看一下数据库更改字符集时的后台处理,我提取了主要的更新部分。 通过以下跟踪过程,我们看到数据库在更改字符集的时候,主要更新了12张数据字典表,修改了数据库的原数据,这也证实了我们以前的说法: 这个更改字符集的操作在本质上并不转换任何数据库字符,只是简单的更新数据库中所有跟字符集相关的信息。

update col$ set charsetid = :1 where charsetform = :2 update argument$ set charsetid = :1 where charsetform = :2 update collection$ set charsetid = :1 where charsetform = :2 update attribute$ set charsetid = :1 where charsetform = :2 update parameter$ set charsetid = :1 where charsetform = :2 update result$ set charsetid = :1 where charsetform = :2 update partcol$ set spare1 = :1 where charsetform = :2 update subpartcol$ set spare1 = :1 where charsetform = :2 update props$ set value$ = :1 where name = :2 update "SYS"."KOTAD$" set SYS_NC_ROWINFO$ = :1 where SYS_NC_OID$ = :2 update seq$ set increment$=:2,minvalue=:3,maxvalue=:4,cycle#=:5,order$=:6, cache=:7,highwater=:8,audit$=:9,flags=:10 where obj#=:1 update kopm$ set metadata = :1, length = :2 where name='DB_FDO'

在这里我们顺便纠正一个由来以及的错误方法. 经常可以在网上看到这样的更改字符集的方法:

1)用SYS用户名登陆ORACLE。 2)查看字符集内容 SQL>SELECT * FROM PROPS$; 3)修改字符集 SQL> update props$ set value$='新字符集' where name='NLS_CHARACTERSET' 4) COMMIT;

我们看到很多人在这个问题上遇到了惨痛的教训,使用这种方式更改字符集,如果你的value$值输入了不正确的字符集,在8i中那么你 的数据库可能会无法启动,这种情况是非常严重的,有时候你必须从备份中进行恢复;如果是在9i中,可以重新启动数据库后再修改回正 确的字符集。但是我们仍然不建议使用这种方式进行任何数据库修改,这是一种极其危险的操作。 实际上当我们更新了字符集,数据库启动时会根据数据库的字符集自动的来修改控制文件的字符集,如果字符集可以识别,更新控制文 件字符集等于数据库字符集;如果字符集不可识别,那么控制文件字符集更新为US7ASCII.

通过更新props$表的方式修改字符集,在Oracle7之后就不应该被使用.

以下是我的测试结果,但是严禁一切不备份的修改研究,即使是对测试库的。

SQL> update props$ set value$='EYGLE' where name='NLS_CHARACTERSET'; 1 row updated. SQL> commit; Commit complete. SQL> select name,value$ from props$ where name like '%NLS%'; NAME VALUE$ ------------------------------ ----------------------------------- NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET EYGLE NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN …. NLS_NCHAR_CHARACTERSET ZHS16GBK NLS_RDBMS_VERSION 8.1.7.1.1 18 rows selected. 重新启动数据库,发现alert.log文件中记录如下操作: Mon Nov 03 16:11:35 2003 Updating character set in controlfile to US7ASCII Completed: ALTER DATABASE OPEN 启动数据库后恢复字符集设置: SQL> update props$ set value$='ZHS16GBK' where name='NLS_CHARACTERSET'; 1 row updated. SQL> commit; Commit complete. SQL> select name,value$ from props$ where name like '%NLS%'; NAME 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_COMP BINARY NLS_NCHAR_CHARACTERSET ZHS16GBK NLS_RDBMS_VERSION 8.1.7.1.1 18 rows selected. 重新启动数据库后,发现控制文件的字符集被更新: Mon Nov 03 16:21:41 2003 Updating character set in controlfile to ZHS16GBK Completed: ALTER DATABASE OPEN

理解了字符集调整的内部操作以后,我们可以轻易的指出,以上的方法是不正确的,通过前面 ” ALTER DATABASE CHARACTER SET” 方式更改字 符集时,Oracle至少需要更改12张数据字典表,而这种直接更新props$表的方式只完成了其中十二分之一的工作,潜在的完整性隐患是可想而知的。

所以,更改字符集尽量要使用正常的途径

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

PL/SQL --> INSTEAD OF 触发器

INSTEAD OF 触发器常用于管理编写不可更新的视图,INSTEAD-OF触发器必须是行级的。

8920
来自专栏峰会SaaS大佬云集

Oracle 数据库入门之----------------------过滤和排序

     EMPNO ENAME    JOB              MGR HIREDATE         SAL       COMM     DEP...

7900
来自专栏技术之路

使用Linq to Sql 创建数据库和表

1.建一个类Article 1 using System.Data.Linq.Mapping; 2 3 4 5 [Table(Name =...

21570
来自专栏乐沙弥的世界

PL/SQL --> 存储过程

存储过程子程序的一种类型,能够完成一些任务,作为schema对象存储于数据库。是一个有名字的PL/SQL代码块,支持接收或不接受参数

9430
来自专栏个人分享

Spark工程开发常用函数与方法(Scala语言)

import org.apache.spark.{SparkContext, SparkConf} import org.apache.spark.sql.{S...

19320
来自专栏乐沙弥的世界

NULL 值与索引(二)

    在NULL值与索引(一)中讲述了null值与索引的一些基本情况。其主要的内容为,基于允许存在null值的索引列,其索引值不会被存储;其次 是由于这个特...

12320
来自专栏乐沙弥的世界

ORA-06502 assigning values from SQL to PL/SQL variables

    最近SQL查询返回的结果给PL/SQL变量出现ORA-06502错误。这个错误的描述是ORA-06502: PL/SQL: numeric or val...

7910
来自专栏乐沙弥的世界

NULL 值与索引(一)

    NULL值是关系数据库系统布尔型(true,false,unknown)中比较特殊类型的一种值,通常称为UNKNOWN或空值,即是未知的,不确定的。由...

33320
来自专栏java达人

mysql left( right ) join使用on 与where 筛选的差异

有这样的一个问题mysql查询使用mysql中left(right)join筛选条件在on与where查询出的数据是否有差异。 可能只看着两个关键字看不出任...

25970
来自专栏转载gongluck的CSDN博客

第8章 基本UDP套接字编程

客户端: #include "../Gnet.h" void do_client(int udpfd, struct sockaddr* pserver_ad...

393110

扫码关注云+社区

领取腾讯云代金券