手工修改OracleXE的字符集——删除重建

一、OracleXE默认字符集的问题

--0.版本及字符集信息

SQL> select * from v$version;

 

BANNER
--------------------------------------------------------------------------------
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Product
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> set linesize 1000
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                                             AL32UTF8
NLS_CALENDAR                                                 GREGORIAN
NLS_DATE_FORMAT                                              DD-MON-RR
NLS_DATE_LANGUAGE                                            AMERICAN
NLS_SORT                                                     BINARY
NLS_TIME_FORMAT                                              HH.MI.SSXFF AM

 

PARAMETER                                                    VALUE
------------------------------------------------------------ -----------------------------
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.2.0.1.0

 

已选择20行。

 

--问题：在字符集AL32UTF8中，一个汉字占用3个字符

SQL> select lengthb('田') from dual;

 

LENGTHB('田')
-------------
            3

 

--导致的问题是：对于字符集是ZHS16GBK的oracle10.2.0.1企业版，所导出的数据，如果带有汉字，就可能导不进去。（而对于国内数据库，ZHS16GBK是常用的字符集设置。）

--例如，某字段在企业版中定义为varchar2(4)，保存数据为'田田'，则导入oraclexe时，该字段定义仍为varchar2(4)，但数据'田田'就需要占用6个字符长度，出现的问题如下所示：

IMP-00019: 由于 ORACLE 错误 12899 而拒绝行
IMP-00003: 遇到 ORACLE 错误 12899
ORA-12899: 列 "TEST"."TEST_TIANYC"."A" 的值太大 (实际值: 6, 最大值: 4)

 

二、解决办法

而字符集AL32UTF8和ZHS16GBK之间没有子集和超集的关系，所以也不能用eygle提供的方法进行修改。我所想到的，是通过手工删除现有数据库，再手工创建字符集为ZHS16GBK的数据库的方法：

C:/Documents and Settings/yuechaotian>sqlplus / as sysdba

 

SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 2月 19 10:50:23 2008

 

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

 

连接到:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

 

--1.创建pfile目的：在手工删除数据库时，会同时删除spfile。这样，在重新创建时，就会由于没有pfile所指向的spfile而提示错误。（当然，初始的pfile文件还有另一个备份：“D:/oraclexe/app/oracle/product/10.2.0/server/config/scripts/init.ora”）

SQL> create pfile from spfile;

 

文件已创建。

 

--2.手工删除数据库

SQL> shut
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup mount
ORACLE 例程已经启动。

 

Total System Global Area  285212672 bytes
Fixed Size                  1287016 bytes
Variable Size              96472216 bytes
Database Buffers          184549376 bytes
Redo Buffers                2904064 bytes
数据库装载完毕。
SQL> drop database;
drop database
*
第 1 行出现错误:
ORA-12719: 操作要求数据库处于 RESTRICTED 模式下

 

SQL> alter system enable restricted session;

 

系统已更改。

 

SQL> drop database;

 

数据库已删除。

 

从 Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production 断开
SQL>

 

--3.手工创建数据库

SQL> exit

 

C:/Documents and Settings/yuechaotian>sqlplus / as sysdba

 

SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 2月 19 11:09:32 2008

 

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

 

已连接到空闲例程。

 

--在 l1t的介绍中 ，谈到数据库名必须仍为XE，不能修改。其实是因为在pfile中指定了参数db_name='XE'。比如，我在创建时候修改pfile中的该参数为'XE2'，然后使用pfile启动并，并执行创建脚本：（在实际使用中，并不需要修改数据库名。所以不需要修改pfile的db_name参数，记得修改下面的创建脚本中的数据库名哦。）

SQL> startup nomount
ORACLE 例程已经启动。

 

Total System Global Area  285212672 bytes
Fixed Size                  1287016 bytes
Variable Size              96472216 bytes
Database Buffers          184549376 bytes
Redo Buffers                2904064 bytes

 

--l1t所提及的不可指定extent management local的问题，确实出现了：

SQL> CREATE DATABASE XE2
  2  CONTROLFILE REUSE
  3  LOGFILE
  4  GROUP 1 'D:/oraclexe/oradata/XE/REDO01.LOG' SIZE 10M,
  5  GROUP 2 'D:/oraclexe/oradata/XE/REDO02.LOG' SIZE 10M,
  6  GROUP 3 'D:/oraclexe/oradata/XE/REDO03.LOG' SIZE 10M
  7  DATAFILE 'D:/oraclexe/oradata/XE/SYSTEM01.DBF' SIZE 400M
  8  AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
  9  SYSAUX DATAFILE 'D:/oraclexe/oradata/XE/SYSAUX01.DBF' SIZE 250M
 10  AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
 11  DEFAULT TEMPORARY TABLESPACE temp
 12  TEMPFILE 'D:/oraclexe/oradata/XE/TEMP01.DBF' SIZE 100M
 13  AUTOEXTEND ON NEXT 100M MAXSIZE 8000M
 14  UNDO TABLESPACE undo
 15  DATAFILE 'D:/oraclexe/oradata/XE/undo01.DBF' SIZE 200M
 16  AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
 17  CHARACTER SET ZHS16GBK
 18  NATIONAL CHARACTER SET AL16UTF16
 19  USER SYS IDENTIFIED BY SYS
 20  USER SYSTEM IDENTIFIED BY SYS
 21  ;
CREATE DATABASE XE2
*
第 1 行出现错误:
ORA-25146: EXTENT MANAGEMENT ?????

 

--出现这个错误的原因，可能是在OracleXE10.2.0.1中，这几个表空间的extent管理必须是local方式的，这样，在创建时就不可指定extent参数。例如初始安装后，查询如下：

SQL> select TABLESPACE_NAME, EXTENT_MANAGEMENT from user_tablespaces;

TABLESPACE_NAME  EXTENT_MANAGEMENT
-----------------------------------
SYSTEM           LOCAL
UNDO             LOCAL
SYSAUX           LOCAL
TEMP             LOCAL
USERS            LOCAL

 

不过这只是猜测，删除这个参数，就可以了：
SQL> CREATE DATABASE XE2
  2  CONTROLFILE REUSE
  3  LOGFILE
  4  GROUP 1 'D:/oraclexe/oradata/XE/REDO01.LOG' SIZE 10M,
  5  GROUP 2 'D:/oraclexe/oradata/XE/REDO02.LOG' SIZE 10M,
  6  GROUP 3 'D:/oraclexe/oradata/XE/REDO03.LOG' SIZE 10M
  7  DATAFILE 'D:/oraclexe/oradata/XE/SYSTEM01.DBF' SIZE 400M
  8  AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
  9  SYSAUX DATAFILE 'D:/oraclexe/oradata/XE/SYSAUX01.DBF' SIZE 250M
 10  AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
 11  DEFAULT TEMPORARY TABLESPACE temp
 12  TEMPFILE 'D:/oraclexe/oradata/XE/TEMP01.DBF' SIZE 100M
 13  AUTOEXTEND ON NEXT 100M MAXSIZE 8000M
 14  UNDO TABLESPACE undo
 15  DATAFILE 'D:/oraclexe/oradata/XE/undo01.DBF' SIZE 200M
 16  AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
 17  CHARACTER SET ZHS16GBK
 18  NATIONAL CHARACTER SET AL16UTF16
 19  USER SYS IDENTIFIED BY SYS
 20  USER SYSTEM IDENTIFIED BY SYS
 21  ;

 

数据库已创建。

 

SQL> select status from v$instance;

 

STATUS
------------
OPEN
  
SQL> @D:/oraclexe/app/oracle/product/10.2.0/server/RDBMS/ADMIN/catalog.sql
...  
PL/SQL 过程已成功完成。  
  
SQL> @D:/oraclexe/app/oracle/product/10.2.0/server/RDBMS/ADMIN/catproc.sql
...  
PL/SQL 过程已成功完成。  

 

--完成之后，看看汉字所占用的字符长度，出错了：

SQL> select lengthb('田田') from dual;

 

LENGTHB('??')
-------------
            2

 

已选择 1 行。

 

--不过没关系，重启后就可以了：

SQL> shut
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

 

Total System Global Area  285212672 bytes
Fixed Size                  1287016 bytes
Variable Size              96472216 bytes
Database Buffers          184549376 bytes
Redo Buffers                2904064 bytes
数据库装载完毕。
数据库已经打开。
SQL> select lengthb('田田') from dual;

 

LENGTHB('田田')
---------------
              4

 

已选择 1 行。

 

--创建新用户test，使用test登录时，还会出现一个错误（此时若执行imp操作时，也会出错）：

SQL> create user test identified by test;

 

用户已创建。

 

SQL> grant dba to test;

 

授权成功。

 

SQL> conn test/test
访问 PRODUCT_USER_PROFILE 时出错
警告: 未加载产品用户概要文件信息!
您需要将 PUPBLD.SQL 作为 SYSTEM 运行
已连接。
SQL>

对于这个错误， magicboy做了个解释。 我们执行一下脚本pupbld.sql，就可以正常连接和imp了：

SQL> conn / as sysdba
已连接。
SQL> alter user system identified by oracle;

 

用户已更改。

 

SQL> conn system/oracle
已连接。
SQL> @D:/oraclexe/app/oracle/product/10.2.0/server/sqlplus/admin/pupbld.sql

...

 

SQL> conn test/test
已连接。

--此时的实例名是xe，而数据库名是xe2。当然，实例名应该也可以通过命令oradim来删除和新建来改变（再次建议：实际应用中，数据库名还是用原来的xe比较好，不需修改）。
SQL> select INSTANCE_NUMBER, INSTANCE_NAME, HOST_NAME, EDITION from v$instance;

 

INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME     EDITION
--------------- ---------------- ------------- -------
              1 xe               YUECHAOTIAN   XE

 

已选择 1 行。

 

SQL> select dbid, name from v$database;

 

      DBID NAME
---------- ---------
1186329897 XE2

 

已选择 1 行。

修改后，就可以实现与oracle企业版中带汉字数据的正常导入导出了。

SQL> host
Microsoft Windows XP [版本 5.1.2600]
(C) 版权所有 1985-2001 Microsoft Corp.

 

C:/Documents and Settings/yuechaotian>imp userid=test/test fromuser=hdtest touser=test file=d:/ab01.dbf

 

Import: Release 10.2.0.1.0 - Production on 星期二 2月 19 13:26:33 2008

 

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

 

连接到: Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

 

经由常规路径由 EXPORT:V10.02.01 创建的导出文件

 

警告: 这些对象由 HDTEST 导出, 而不是当前用户

 

已经完成 ZHS16GBK 字符集和 AL16UTF16 NCHAR 字符集中的导入
. 正在将 HDTEST 的对象导入到 TEST
. . 正在导入表                          "AB01"导入了        2580 行
成功终止导入, 没有出现警告。

 

C:/Documents and Settings/yuechaotian>exit

SQL>

不过呢，OracleXE的控制台是不能用拉。可能还需要修改点东西，有空的时候我再找找原因。

其实可以发现，重建数据库后，监听里已经没有http的的信息了，而原来是有的：

C:/Documents and Settings/IBM>lsnrctl status

LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 19-2月 -2008 15:25:12

Copyright (c) 1991, 2005, Oracle.  All rights reserved.

正在连接到 (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))
LISTENER 的 STATUS
------------------------
别名                      LISTENER
版本                      TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
启动日期                  19-2月 -2008 14:27:27
正常运行时间              0 天 0 小时 57 分 44 秒
跟踪级别                  off
安全性                    ON: Local OS Authentication
SNMP                      OFF
默认服务           XE
监听程序参数文件          D:/oraclexe/app/oracle/product/10.2.0/server/network/admin/listener.ora
监听程序日志文件          D:/oraclexe/app/oracle/product/10.2.0/server/network/log/listener.log
监听端点概要...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(PIPENAME=//./pipe/EXTPROC_FOR_XEipc)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=yuechaotian)(PORT=1523)))
服务摘要..
服务 "CLRExtProc" 包含 1 个例程。
  例程 "CLRExtProc", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
服务 "PLSExtProc" 包含 1 个例程。
  例程 "PLSExtProc", 状态 UNKNOWN, 包含此服务的 1 个处理程序...
服务 "XEXDB" 包含 1 个例程。
  例程 "xe", 状态 READY, 包含此服务的 1 个处理程序...
服务 "XE_XPT" 包含 1 个例程。
  例程 "xe", 状态 READY, 包含此服务的 1 个处理程序...
服务 "xe" 包含 1 个例程。
  例程 "xe", 状态 READY, 包含此服务的 1 个处理程序...
命令执行成功

C:/Documents and Settings/IBM>