使用internal_convert修改oracle 10g 数据库字符集

最新推荐文章于 2023-05-05 18:10:47 发布

xiaoping8411

最新推荐文章于 2023-05-05 18:10:47 发布

阅读量1.3k

点赞数

分类专栏： ORACLE 文章标签： oracle 数据库 character database integer sql

ORACLE 专栏收录该内容

18 篇文章 0 订阅

订阅专栏

SQL> startup mount
ORACLE instance started.

Total System Global Area 117440512 bytes
Fixed Size 787728 bytes
Variable Size 91224816 bytes
Database Buffers 25165824 bytes
Redo Buffers 262144 bytes
Database mounted.
SQL> show parameter processes

NAME TYPE VALUE
------------------------------------ ----------- --------------------------
aq_tm_processes integer 10
db_writer_processes integer 1
gcs_server_processes integer 0
job_queue_processes integer 10
log_archive_max_processes integer 2
processes integer 150


打开数据库到restricted状态

SQL> alter system enable restricted session;

System altered.

SQL> alter database open;

Database altered.

查看当前数据库字符集:
SQL> col name for a30
SQL> col value$ for a30
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
......

20 rows selected.

这里为了方便区分trace文件名，把trace文件加上标识

SQL> alter session set tracefile_identifier='cs';

Session altered.

SQL> alter session set sql_trace=true;

Session altered.

SQL> alter database character set internal_convert zhs16gbk;

Database altered.

alert信息:
~~~~~~~~~~~~~~~~~~~~~
Wed Jan 16 14:49:47 2008
alter database character set internal_convert zhs16gbk
Wed Jan 16 14:49:50 2008
Private_strands 7 at log switch
Thread 1 advanced to log sequence 38
  Current log# 2 seq# 38 mem# 0: D:\ORACLE\ORADATA\ORCL\REDO02.LOG
Wed Jan 16 14:49:52 2008
Updating character set in controlfile to ZHS16GBK
Synchronizing connection with database character set information
Wed Jan 16 14:49:52 2008
Published database character set on system events channel
SYS.WRI$_DBU_HWM_METADATA (LOGIC) - CLOB representation altered
SYS.WRI$_DBU_FEATURE_METADATA (INST_CHK_LOGIC) - CLOB representation altered
SYS.WRI$_DBU_FEATURE_METADATA (USG_DET_LOGIC) - CLOB representation altered
SYS.WRI$_DBU_FEATURE_USAGE (FEATURE_INFO) - CLOB representation altered
SYS.SCHEDULER$_EVENT_LOG (ADDITIONAL_INFO) - CLOB representation altered
SYS.RULE$ (CONDITION) - CLOB representation altered
SYS.METASTYLESHEET (STYLESHEET) - CLOB representation altered
Refreshing type attributes with new character set information

alert中的信息与上面的相同，ORACLE在内部转换CLOB字段相关的表为新字符集，这个在trace文件中可以很清楚得看到。
这里就不贴trace信息了。有兴趣自己trace一下。

查看修改后的字符集：
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
......

SQL> alter system disable restricted session;

System altered.

到此，字符集修改完成。