有网友在ITPUB上问到“sqlplus下如何获取shell的return code”
可以用以下的办法：
在SHELL中把结果重定向到一个文件。
举个例子：
所有的测试文件都放在$HOME下
func.sh是你要执行操作的SHELL
#!/bin/sh
exit 2
test.sh的功能就是调用func.sh
#!/bin/sh
$HOME/func.sh
echo "define VAR_TEST=$?" > $HOME/test.sql
在SQLPLUS中
SQL > host $HOME/test.sh
SQL > @$HOME/test.sql
现在就定义了一个SQLPLUS的变量VAR_TEST。
SQL > select ‘&VAR_TEST’ x from dual;
old 1: select ‘&VAR_TEST’ x from dual
new 1:select ‘2′ x from dual
x
--
2
一客户重启主机，启动数据库之后，客户端连接报ORA-12541：没有监听器的错误
环境：ORACLE 10.2.0.1 RAC AIX 5.3
进行测试，发现结点1不正常。客户端通过PUBLIC IP可以连接，但通过VIP不能进行连接，报ORA-12541错误
检查结点1的VIP，正常。在客户端PING VIP，正常。TNSPING VIP，不通。
检查监听配置，正常。
重启监听，故障依旧。
在客户端（WINDOWS系统），用arp -a命令检查发现，结点1的PUBLIC IP和VIP的MAC地址不一致。怀疑是其他机器占用了此IP。
在客户端用nbtstat -A VIP地址，发现是一WINDOWS系统机器占用了此IP，并得到机器名。
客户维护人员解决IP地址冲突，故障排除。
故障解决很快，但此故障引发的问题值得紧记：在管理数据库的同时，别忘网络的管理。特记之。
大家都知道，ORACLE实例在启动时，或使用命令ALTER SYSTEM REGISTER 或每隔一分钟,实例的PMON会向监听进行注册，告知监听，实例的服务名，实例名等信息。
不同的平台有不同的行为，本文所描述的是在LINUX下的行为。ORACLE版本为10.2.0.1。
讲到动态注册，跟监听密切相关，下面先看看监听的行为：
监听在启动时，会从$ORACLE_HOME/network/admin/listener.ora读取监听配置，如果该文件不存在，则监听会在主机名对应的IP和1521端口上进行监听。如果主机名在/etc/hosts里没有配置（或不能通过DNS解析---这是我的猜想，没有验证），则在等待较长一段时间后，将在所有的地址上（0.0.0.0:1521）进行监听，但此时实例并不会进行动态注册，客户端可通过主机的任意IP地址连接，但均会报ORA-12514错误。除非设置LOCAL_LISTENER参数，将本地LISTENER地址指向本机的任意一IP。
如果存在listener.ora文件，则监听会根据该文件配置内容进行启动。如果主机名在/etc/hosts没有条目，监听 启动比较慢（可能是监听起来后，在根据主机名作什么操作），因此必须要保证主机名要在/etc/hosts中有记录。
同一个网络接口（网卡）上，如果绑定了两个或以上的IP地址，则监听这样的网络接口时，最多只能使用一个主机名，比如：
[oracle@xty ~]$ cat /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1 localhost.localdomain localhost
192.168.0.114 xty
192.168.0.115 xty_vip
192.168.0.116 xty_vip2
这里xty和xty_vip对应的IP绑定在同一网卡上
cat listener.ora
# Generated by Oracle configuration tools.
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /u01/app/oracle/oracle/product/10.2.0/db_1)
(PROGRAM = extproc)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xty_vip )(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = xty )(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = xty_vip2 )(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
启动监听时，报下面的错误：
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/oracle/product/10.2.0/db_1/network/admi
n/listener.ora
Log messages written to /u01/app/oracle/oracle/product/10.2.0/db_1/network/log/l
istener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.115)(PORT=1521
)))
Error listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xty)(PORT=1521)))
TNS-12542: TNS:address already in use
TNS-12560: TNS:protocol adapter error
TNS-00512: Address already in use
Linux Error: 98: Address already in use
Listener failed to start. See the error message(s) above…
将listener.ora中的(ADDRESS = (PROTOCOL = TCP)(HOST = xty )(PORT = 1521))行，改为(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.114 )(PORT = 1521))，则监听能够正常启动
下面再来看一下PMON向监听注册实例的行为：
在缺省情况下（也就是没有LOCAL_LISTENER参数配置的情况下），PMON会根据主机名(hostname)，查找其IP地址，通常是在/etc/hosts中找对应的条目，如果没有找到hostname的IP地址，则PMON不会注册，同时，必须是本机的IP地址，PMON才能注册。比如在/etc/hosts中将hostname对应的IP地址改为其他非本机的IP地址，PMON也不会进行注册。PMON根据hostname找到IP后，同时判断这个是本机IP，则会通过这个IP连接至监听进行注册。如果listener没有监听这个IP，则PMON也不会注册，因为通过这个IP连接不上监听。
举一个简单的例子，现有两台IBM小机，作双机热备，双机采用HACMP。在监听设置中，只监听了HA的服务IP地址，而hostname对应的IP地址为服务IP绑定的网卡上的另一个地址。在这种情况下，PMON不能进行动态注册，在客户端连接这个ORACLE服务器时，将会报ORA-12514错误。
解决上面提到的这个问题的办法，除了静态注册（本文主要讨论动态注册），还有两种办法，第一种我个人认为最好的一种，是在LISTENER上监听两个IP地址，类似于下面这样：
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = xty_vip )(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.114 )(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
另一个办法就是设置LOCAL_LISTENER参数，假如LISTENER只监听了xty_vip（192.168.0.115）这个地址，则通过下面的命令设置LOCAL_LISTENER：
ALTER SYSTEM SET LOCAL_LISTENER=’ (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.115 )(PORT = 1521))’
这里建议使用IP地址，特别是对RAC，后文细述。
LOCAL_LISTENER使PMON改变用hostname连接LISTENER进行注册的默认行为。改而用LOCAL_LISTENER参数指定的地址连接LISTENER进行注册。当然LOCAL_LISTENER指定的IP地址必须是本机的IP地址。如果是非本机IP，则会忽略此参数，但是会从前一个已注册的监听中取消注册。
与LOCAL_LISTENER对应的参数有REMOTE_LISTENER参数。REMOTE_LISTENER使PMON在远程（即非本机）监听上进行注册，这个参数在RAC中经常使用（用于负载均衡）。
下面来作一个测试：
主机1，LINUX AS4
主机2，Windows 2003，IP地址：192.168.0.100
,在主机2上启动监听：
D:\oracle\product\10.2.0\db_1\BIN>lsnrctl start
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 02-2月 -2008 09:5
6:44
Copyright (c) 1991, 2005, Oracle. All rights reserved.
启动tnslsnr: 请稍候…
TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Production
写入d:\oracle\product\10.2.0\db_1\network\log\listener.log的日志信息
监听: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dreamf)(PORT=1521)))
正在连接到 (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER 的 STATUS
------------------------
别名 LISTENER
版本 TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ
ction
启动日期 02-2月 -2008 09:56:47
正常运行时间 0 天 0 小时 0 分 3 秒
跟踪级别 off
安全性 ON: Local OS Authentication
SNMP OFF
监听程序日志文件 d:\oracle\product\10.2.0\db_1\network\log\listener
监听端点概要…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dreamf)(PORT=1521)))
监听程序不支持服务
命令执行成功
在主机1上启动监听：
[oracle@xty ~]$ lsnrctl start
LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 01-FEB-2008 17:28:57
Copyright (c) 1991, 2005, Oracle. All rights reserved.
Starting /u01/app/oracle/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait…
TNSLSNR for Linux: Version 10.2.0.1.0 - Production
System parameter file is /u01/app/oracle/oracle/product/10.2.0/db_1/network/admi
n/listener.ora
Log messages written to /u01/app/oracle/oracle/product/10.2.0/db_1/network/log/l
istener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.115)(PORT=1521
)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.114)(PORT=1521
)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=xty_vip)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 10.2.0.1.0 - Production
Start Date 01-FEB-2008 17:28:57
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/oracle/product/10.2.0/db_1/network/adm
in/listener.ora
Listener Log File /u01/app/oracle/oracle/product/10.2.0/db_1/network/log
/listener.log
Listening Endpoints Summary…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.115)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.114)(PORT=1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary…
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service…
The command completed successfully
[oracle@xty ~]$
然后在主机1上启动数据库。输入命令：
alter system set remote_listener=’(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.100)(PORT=1521))’;
在主机2上查看LISTENER的状态
正在连接到 (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER 的 STATUS
------------------------
别名 LISTENER
版本 TNSLSNR for 32-bit Windows: Version 10.2.0.1.0 - Produ
ction
启动日期 02-2月 -2008 09:56:47
正常运行时间 0 天 0 小时 16 分 54 秒
跟踪级别 off
安全性 ON: Local OS Authentication
SNMP OFF
监听程序日志文件 d:\oracle\product\10.2.0\db_1\network\log\listener.log
监听端点概要…
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=dreamf)(PORT=1521)))
服务摘要..
服务 "XTY" 包含 1 个例程。
例程 "XTY1", 状态 READY, 包含此服务的 1 个处理程序…
服务 "XTY1XDB" 包含 1 个例程。
例程 "XTY1", 状态 READY, 包含此服务的 1 个处理程序…
服务 "XTY_XPT" 包含 1 个例程。
例程 "XTY1", 状态 READY, 包含此服务的 1 个处理程序…
命令执行成功
在主机2上再执行lsnrctl service命令：
D:\oracle\product\10.2.0\db_1\BIN>lsnrctl service
LSNRCTL for 32-bit Windows: Version 10.2.0.1.0 - Production on 02-2月 -2008 10:
5:21
Copyright (c) 1991, 2005, Oracle. All rights reserved.
正在连接到 (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
服务摘要..
服务 "XTY" 包含 1 个例程。
例程 "XTY1", 状态 READY, 包含此服务的 1 个处理程序…
处理程序:
"DEDICATED" 已建立:0 已拒绝:0 状态:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=xty)(PORT=1521))
服务 "XTY1XDB" 包含 1 个例程。
例程 "XTY1", 状态 READY, 包含此服务的 1 个处理程序…
处理程序:
"D000" 已建立:0 已被拒绝:0 当前: 0 最大: 1022 状态: ready
DISPATCHER <machine: xty, pid: 2996>
(ADDRESS=(PROTOCOL=tcp)(HOST=xty)(PORT=32801))
服务 "XTY_XPT" 包含 1 个例程。
例程 "XTY1", 状态 READY, 包含此服务的 1 个处理程序…
处理程序:
"DEDICATED" 已建立:0 已拒绝:0 状态:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=TCP)(HOST=xty)(PORT=1521))
命令执行成功
主机1上的实例已经成功注册到主机2上的监听
在主机2上的TNSNAMES.ORA中有：
XTY_R =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.100)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = xty)
)
)
在主机2上连接XTY_R（这个实例实际运行在主机1上）
D:\oracle\admin\XJ\bdump>sqlplus test/test@xty_r
SQL*Plus: Release 9.2.0.1.0 - Production on 星期六 2月 2 10:19:41 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
ERROR:
ORA-12535: TNS: 操作超时
请输入用户名:
报了ORA-12536错误。
从上面的lsnrctl service命令可以查看到REMOTE SERVER的地址为：
(ADDRESS=(PROTOCOL=TCP)(HOST=xty)(PORT=1521))
因此客户端连接时，LISTENER判断这是个远程SERVER，会将这个地址返回给客户，客户端再去连接这个地址。但这里HOST=xty，客户端不能解析这个地址，所以就报超时错误。
但如果在主机1上的数据库中执行下面的命令：
alter system set local_listener=’(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.115)(PORT=1521))’;
我们再看看主机2上的LISTENER SERVICE：
正在连接到 (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
服务摘要..
服务 "XTY" 包含 1 个例程。
例程 "XTY1", 状态 READY, 包含此服务的 1 个处理程序…
处理程序:
"DEDICATED" 已建立:0 已拒绝:0 状态:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.115)(PORT=1521))
服务 "XTY1XDB" 包含 1 个例程。
例程 "XTY1", 状态 READY, 包含此服务的 1 个处理程序…
处理程序:
"D000" 已建立:0 已被拒绝:0 当前: 0 最大: 1022 状态: ready
DISPATCHER <machine: xty, pid: 2996>
(ADDRESS=(PROTOCOL=tcp)(HOST=xty)(PORT=32801))
服务 "XTY_XPT" 包含 1 个例程。
例程 "XTY1", 状态 READY, 包含此服务的 1 个处理程序…
处理程序:
"DEDICATED" 已建立:0 已拒绝:0 状态:ready
REMOTE SERVER
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.115)(PORT=1521))
命令执行成功
注意看到上面REMOTE SERVER已经变成了 (ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.115)(PORT=1521))
这个时候再次连接，由成功连接到ORACLE服务器上：
D:\oracle\admin\XJ\bdump>sqlplus test/test@xty_r
SQL*Plus: Release 9.2.0.1.0 - Production on 星期六 2月 2 10:39:25 2008
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>
在RAC中，服务器开启了负载均衡，则客户端有时连接时会出现ORA-12514错误，这里需要设置LOCAL_LISTENER参数，以解决该问题。
注意：在通过REMOTE_LISTENER参数向远程监听注册时，本地的LISTENER也需要处于启动状态，否则监听中服务的状态为BLOCKED状态：
正在连接到 (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
服务摘要..
服务 "XTY" 包含 1 个例程。
例程 "XTY1", 状态 READY, 包含此服务的 1 个处理程序…
处理程序:
"DEDICATED" 已建立:1 已拒绝:0 状态:blocked
REMOTE SERVER
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.115)(PORT=1521))
服务 "XTY1XDB" 包含 1 个例程。
例程 "XTY1", 状态 READY, 包含此服务的 1 个处理程序…
处理程序:
"D000" 已建立:0 已被拒绝:0 当前: 0 最大: 1022 状态: ready
DISPATCHER <machine: xty, pid: 2996>
(ADDRESS=(PROTOCOL=tcp)(HOST=xty)(PORT=32801))
服务 "XTY_XPT" 包含 1 个例程。
例程 "XTY1", 状态 READY, 包含此服务的 1 个处理程序…
处理程序:
"DEDICATED" 已建立:1 已拒绝:0 状态:blocked
REMOTE SERVER
(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.115)(PORT=1521))
命令执行成功
而这个时候客户端连接报ORA-12516错误
另外，数据库实例版本必须与LISTENER版本兼容，否则不能进行动态注册。
本文主要是通过实验和分析网络包，然后进行总结的结果，没有参考相关的理论文档。有所错误在所难免，欢迎讨论。
众所周知，如果一个库没有设置为force logging，而这个库在归档模式下，对表的插入操作如果采用APPEND模式，并且表设置为nologging则不会为插入的数据产生日志。那么对LOB列的存储也设置为NOLOGGING，会产生什么样的结果？
测试环境：Oracle 9.2.0.1 for Win，非归档模式
create table test1.t1(id int not null, out_row clob)
lob(out_row) store as (disable storage in row nocache nologging);
关闭数据库，备份数据文件users01.dbf
启动数据库，将表中插入数据：
insert into test1.t1 select rownum,rpad(’x',5000,’x') from dba_objects where rownum<=100;
select * from test1.t1;
能够正常返回100行数据。
关闭数据库，用备份的文件还原文件users01.dbf，启动数据库时报错：
数据库装载完毕。
ORA-01113: ?? 5 ??????
ORA-01110: ???? 5: ‘D:\ORACLE\ORADATA\XJ\USERS01.DBF’
SQL> recover datafile 5;
完成介质恢复。
SQL> alter session set nls_language=american;
Session altered.
SQL> alter database open;
Database altered.
SQL> select count(*) from test1.t1;
COUNT(*)
----------
100
SQL> select * from test1.t1;
ERROR:
ORA-01578: ORACLE data block corrupted (file # 5, block # 61)
ORA-26040: Data block was loaded using the NOLOGGING option
ORA-01110: data file 5: ‘D:\ORACLE\ORADATA\XJ\USERS01.DBF’
由此可以看出，在此前的插入操作中，没有对LOB数据产生日志。
我们将test1.t1删除，再重新创建，只是将LOB设置为LOGGING。
SQL> drop table test1.t1;
表已丢弃。
SQL> create table test1.t1 (id number not null,out_row clob)
2 lob (out_row) store as (disable storage in row nocache logging);
重复上步实验过程，发现LOGGING模式下的LOB能够正常恢复。
将数据库设置为归档模式，重复以上测试过程，发现NOLOGGING模式下的LOB仍然不能恢复。
将数据库设置为FORCE LOGGING模式，重复以上测试过程，发现NOLOGGING模式下的LOB能够正常恢复。
经过进一步测试，对LOB字段进行UPDATE也会产生上述实验结果。
在这个测试中使用了一个比较“笨”的办法。观察LOB列是否产生日志，可以观察redo size和分析日志文件进行。在此不在细述。
经过测试，发现IN ROW的LOB列与表中的其他列数据是一致的LOGGING行为。
注意：LOB列如果设置为CACHE，则只能是LOGGING模式。
由此引出一个问题，由于对LOB列设置为NOLOGGING，虽然能够提高数据插入速度，为数据恢复设置了一道难关。需要此种情况下的备份恢复策略，或者将LOB列设置为LOGGING模式。
前文主要讲到的是执行DML的字符集转换，下面再讨论检索数据时的字符集转换，还是先看测试：
先将NLS_LANG设置为默认值ZHS16GBK
SQL> insert into t1 values (1,’中’,'中’);
已创建 1 行。
SQL> commit;
提交完成。
SQL> select * from t1;
ID AA BB
---------- -------------------- ----------------------------------------
1 中 中
从抓取的网络包中找到返回的数据：
00000030 01 3D 00 00 06 00 00 00 00 00 .=……..
00000040 10 17 3A 08 C0 CA 9B 07 F7 10 15 1A EA 23 F7 68 ..:……….#.h
00000050 DD 85 78 6C 01 1C 0D 22 36 52 00 00 00 03 00 00 ..xl…"6R……
00000060 00 39 02 00 00 81 16 00 00 00 00 00 00 00 00 00 .9…………..
00000070 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 01 …………….
00000080 02 02 00 00 00 02 49 44 00 00 00 00 00 00 00 00 ……ID……..
00000090 01 80 00 00 14 00 00 00 00 00 00 00 00 00 00 00 …………….
000000A0 00 00 00 00 00 0054 0301 14 00 00 00 01 02 02 ……T………
000000B0 00 00 00 02 41 41 00 00 00 00 00 00 00 00 01 80 ….AA……….
000000C0 00 00 28 00 00 00 00 00 00 00 00 10 00 00 00 00 ..(………….
000000D0 00 00 00 00D0 0702 14 00 00 00 01 02 02 00 00 …………….
000000E0 00 02 42 42 00 00 00 00 00 00 00 00 07 00 00 00 ..BB…………
000000F0 07 78 6C 01 1C 0D 22 36 06 02 03 00 00 00 01 00 .xl…"6……..
00000100 00 00 00 00 00 00 00 00 00 00 07 02 C1 02 02D6…………….
00000110 D0024E 2D08 06 00 F2 DF 02 00 00 00 00 00 02 ..N-…………
00000120 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 …………….
00000130 00 00 00 04 01 00 00 00 01 00 00 00 00 00 00 00 …………….
00000140 00 00 02 00 0E 00 03 00 00 00 00 00 07 28 00 00 ………….(..
00000150 04 00 00 16 00 00 00 01 00 00 00 00 00 00 2C 00 …………..,.
00000160 00 01 00 00 00 00 00 00 00 00 00 00 00 00 00 00 …………….
00000170 00 00 00 …
上面展示的是返回的数据。红色分别为AA列和BB列的字符集ID：
SQL> select nls_charset_name(to_number(’0354′,’xxxx’)) from dual;
NLS_CHARSET_NAME(TO_NUMBER(’0354′,’XXXX’
----------------------------------------
ZHS16GBK
SQL> select nls_charset_name(to_number(’07D0′,’xxxx’)) from dual;
NLS_CHARSET_NAME(TO_NUMBER(’07D0′,’XXXX’
----------------------------------------
AL16UTF16
蓝色部分是列数据，D6 D0为ZHS16GBK编码的“中”，而4E 2D为AL16UTF16编码的“中”字，数据原样从数据库中返回。这两个不同的编码，最后显示的结果均为“中”字。由于数据库字符集ZHS16GBK与客户端相同，客户端没有对数据作转换，而国家字符集的“中”字，要转换为ZHS16GBK，再最终由客户端程序（SQLPLUS）显示出来。
下面把NLS_LANG设置为AMERICAN_AMERICA.US7ASCII，再进行同样的测试，发现，返回的网络包是一样，即服务器端返回的数据是一样的，并没有因为NLS_LANG的不同而不同，因此转换仍然是发生在客户端。在这次测试中，将服务器返回的数据，转换成US7ASCII编码，出现了乱码，显示为?号
再将NLS_LANG设置为AMERICAN_AMERICA.UTF8，看看返回的结果
SQL> select * from t1;
ID AA BB
---------- -------------------- --------------------
1 涓? 涓
这次是出现了将“中”字转换成了其他汉字。为什么是转成了这个“涓”字，在此不在细述。
下面把NLS_LANG设置为AMERICAN_AMERICAN.UTF8，但增加了一个环境变量NLS_NCHAR=ZHS16GBK
SQL> select * from t1;
ID AA BB
---------- -------------------- --------------------
1 涓? 中
在本次测试中，字符集为国家字符集AL16UTF16的列BB显示了正确的结果。这说明客户端OCI库在转换时，对国家字符集是根据NLS_NCHAR进行转换的，在这个测试中NLS_NCHAR为ZHS16GBK，将AL16UTF16编码正确地转换到了ZHS16GBK编码。
再作一个测试，将NLS_LANG设置为AMERICAN_AMERICA.ZHS16GBK，将NLS_NCHAR设置为AL16UTF16
SQL> select * from t1;
ID AA BB
---------- -------------------- -----------
1 中 N-
由于NLS_NCHAR与国家字符集相同，因此对国家字集符的列没有作转换，直接返回。“中”字的AL16UTF16的编码为 4E 2D，在客户端操作系统中，正好是英文字符“N”和“-”的编码
结论：
在客户端向服务器端提交SQL语句时，客户端根据NLS_LANG和服务器数据库字符集，对SQL中的字符进行转换处理。如果NLS_LANG设置的字符集与服务器数据库字符集相同，不作转换，否则要转换成服务器端字符符。如果有国家字符集，客户端不作处理，由服务器端再将其转换为国家字符集。
在查询数据时，服务器端原服务器端的编码返回数据，由客户端根据返回的元数据中的字符集与NLS_LANG和NLS_NCHAR的设置进行比较。如果NLS_NCHAR没有设置，则其默认值为NLS_LANG中的字符集设置。如果数据中的字符集与客户端设置一致，不进行转换，否则要进行转换。国家字符集的转换根据NLS_NCHAR设置进行转换。
根据这个结论，再推断出EXPORT和IMPORT时的字符集转换行为：
在EXPORT时，EXP程序本身也是一个普通的客户端程序，因此在执行导出时也会按NLS_LANG和NLS_NCHAR的设置进行字符集转换。然后在DMP文件记录导出时客户端的字符集。
在IMPORT时，如果DMP文件记录的字符集与客户端字符集不一样，需要将其数据转换为客户端的字符集，然后在导入到库中时，由ORACLE的客户端OCI库按前述规则，根据NLS_LANG和服务器端字符集的比较，进行了转换。
上一篇讲到普通字符串的转换，本篇将讲到国家字符集字符串的转换：
客户端的NLS_LANG为默认值，即ZHS16GBK：
SQL> create table t1 ( id number ,aa varchar2(20),bb nvarchar2(20));
表已创建。
SQL> insert into t1 values (1,’中’,'中’);
已创建 1 行。
捕获的网络包如下：
00000090 00 00 00 00 00 00 EA 4E DB 00 AC 0D DC 00 00 00 …….N……..
000000A0 00 00 23 69 6E 73 65 72 74 20 69 6E 74 6F 20 74 ..#insert.into.t
000000B0 31 20 76 61 6C 75 65 73 20 28 31 2C 27D6 D027 1.values.(1,’..’
000000C0 2C 27D6 D027 29 01 00 00 00 01 00 00 00 00 00 ,’..’)……….
SQL> select dump(aa) aa,dump(bb) bb from t1;
AA BB
------------------------------ ------------------------------
Typ=1 Len=2: 214,208 Typ=1 Len=2: 78,45
客户端发送给数据库的SQL语句，两个“中”字均为D6 D0，但服务器对NVARCHAR2类似的列作了转换，将其从ZHS16GBK编码转换为AL16UTF16，转换后的结果为10进制78，45,即16进制的4E 2D
因此对于国家字符集，客户端在提交SQL时实际并不区分是否国家字符集，统一将SQL中的字符转换为数据库字符集，服务器端再将国家字符集的列，从数据集字符集转换为国家字符集。因此，我们可以设想，如果数据库字符集与国家字符集不兼容，会发生什么？或者说是从数据库字符集转换为国家字符集是不是也会出现问题？我们用另一个数据库测试一下：
SQL> select * from nls_database_parameters where parameter like ‘%CHARACTERSET%’
;
PARAMETER VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET US7ASCII
NLS_NCHAR_CHARACTERSET AL16UTF16
将客户端的NLS_LANG设置为AMERICAN_AMERICA.US7ASCII
SQL> create table t1 (id number,aa varchar2(20),bb nvarchar2(20));
SQL> insert into t1 values (1,’中’,'中’);
1 row created.
SQL> select dump(aa) aa,dump(bb) bb from t1;
AA BB
------------------------------ ------------------------------
Typ=1 Len=2: 214,208 Typ=1 Len=4: 0,86,0,80
注意看这里dump出的结果，与前一个库dump出的结果，aa列是一样的，而bb列dump出来变成了10进制的0,86,0,80。我们看看这个值是怎么来的：
1.客户端NLS_LANG与数据库字符集相同，因此在客户端并没对SQL中的字符进行转换。
2.服务器在执行SQL时，将bb列的值从数据库字符集编码（10进制214，208）转换为AL16UTF16编码（这种编码每个字符为固定的两字节）。由于数据库字符集为单字节字符集，在转换时认为是两个字符，同时US7ASCII字符的高位应该为0，而214-128=86，208-128=80.因此转换后其结果就为字符串“VP"了：
SQL> select * from t1;
ID AA BB
---------- -------------------- --------------------
1 中 VP
因此，如果选择了错误的数据库字符集，虽然可以通过设置NLS_LANG将客户端字符集设置为与服务器字符集一致，但国家字符集却有可能不能正常地从数据库字符集转换为国家字符集。
下篇要讨论的是数据查询时和数据导出时的字符集转换。
字符集是一个老生常谈的问题了。论坛中很多贴子探讨过这个问题，这个问题的引起，绝大部分是因为“乱码”。而乱码是由于客户端与服务器的字符集的不同进行字符集转换而引起的。不过很多贴子提到了转换，却没有提到这个转换是在哪个阶段和哪里发生的？是在服务器向块里写入数据的时候吗？在客户端还是在服务器端？
正确的答案是，普通字符串转换发生在客户端（具体来说是由OCI LIBRARY完成的），国家字符串经过两次转换，第一次发生在客户端，第二次发生在服务器端。下面做个测试：
连接到:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> select * from nls_database_parameters where parameter like ‘%CHARACTERSET%’;
PARAMETER VALUE
------------------------------ ------------------------------
NLS_CHARACTERSET ZHS16GBK
NLS_NCHAR_CHARACTERSET AL16UTF16
SQL> create table t1(a varchar2(100));
表已创建。
SQL>
SQL> insert into t1 values (’中’);
已创建 1 行。
SQL>
在本次连接中，我没有设置NLS_LANG变量。则客户端字符集为操作系统的缺省字符集ZHS16GBK。通过捕获网络包，可以发现客户端传送给客户端的数据（不能上传图片，郁闷）：
00000090 00 00 00 00 00 00 00 00 00 00 00 28 DB 00 01 1C ………..(….
000000A0 69 6E 73 65 72 74 20 69 6E 74 6F 20 74 31 20 76 insert.into.t1.v
000000B0 61 6C 75 65 73 20 28 27D6 D027 29 01 00 00 00 alues.(’..’)….
000000C0 01 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 …………….
注意红色的部分，16进制D6 D0正是“中”字的GBK编码。（关于怎么获取汉字的各种编码，暂且略过，如有需要再交流）
现在我们退出SQLPLUS，设置环境变量NLS_LANG：
SQL> rollback;
回退已完成。
SQL> exit
从 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
断开
C:\Documents and Settings\Administrator>set nls_lang=american_america.us7ascii
C:\Documents and Settings\Administrator>sqlplus test/test@dmdb
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Jan 28 00:48:41 2008
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Real Application Clusters, OLAP and Data Mining options
SQL> insert into t1 values (’中’);
1 row created.
抓获的网络包发现，在SQL提交给服务器之前已经转换了。OCI库认为提交过来的编码是US7ASCII，因此要将转换为服务器端的ZHS16GBK编码，然而“中”的编码即16进制D6 D0并不是有效的US7ASCII编码，所以ORACLE OCI就转为了转省值3F3F（US7ASCII是单字节字符集，会认为“中”字是两个字符，因此为有两个3F) 这就是“??”号的由来。
00000090 00 00 00 00 00 00 00 00 00 00 00 C8 1D FF 00 1C …………….
000000A0 69 6E 73 65 72 74 20 69 6E 74 6F 20 74 31 20 76 insert.into.t1.v
000000B0 61 6C 75 65 73 20 28 273F 3F27 29 01 00 00 00 alues.(’??’)….
000000C0 01 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 …………….
我们再看看将客户端NLS_LANG设置为simplified chinese_china.zhs16cgb231280会发生什么：
SQL> insert into t1 values (’中’);
已创建 1 行。
00000090 00 00 00 00 00 00 00 00 00 00 00 00 EC 01 01 1C …………….
000000A0 69 6E 73 65 72 74 20 69 6E 74 6F 20 74 31 20 76 insert.into.t1.v
000000B0 61 6C 75 65 73 20 28 27D6 D027 29 01 00 00 00 alues.(’..’)….
000000C0 01 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 …………….
嗯，这里仍然是D6 D0，我们知道ZHS16GBK近似于ZHS16CGB231280超级。“中”对两种字符集来说，都是同一个编码。
看看我们使用生僻字会发生什么：
SQL> insert into t1 values (’喫’);
ERROR:
ORA-01756: 引号内的字符串没有正确结束
居然没有捕获到这个INSERT INTO语句提交到服务器的网络吧。由于在客户端要将“喫”字从ZHS16GB231280转换为ZHS16GBK，但这个字并不是一个有效的GB2312编码的字。但为什么出现了ORA-01756？转换过程认为“喫”字是GB2312编码，而操作系统传过来的编码是16进制86 CB，GB2312的编码，每个字节都是大于A1，因此认为第1个字节是一个8位的单字符，下一个字节大于A1，因此转换过程就将CB和下一个字节“’”合起来成为一个GB2312的双字节字符，因此就造成了这个错误信息。然而下面的语句是可以通过的：
SQL> insert into t1 values (’喫1′);
已创建 1 行。
抓获的网络包却发现是下面的结果：
00000090 00 00 00 00 00 00 00 00 00 00 00 10 EC 01 01 1D …………….
000000A0 69 6E 73 65 72 74 20 69 6E 74 6F 20 74 31 20 76 insert.into.t1.v
000000B0 61 6C 75 65 73 20 28 273F A3 BF27 29 01 00 00 alues.(’?..’)…
000000C0 00 01 00 00 00 00 00 00 00 00 00 00 00 00 00 00 …………….
验证了上面的观点。第1字节被作为一个单字节字符转换，但是也不能转换为GBK字符，因此就转为了3F，但后面的两个字节仍然不是有效的GBK编码，就转为了A3 BF（全角的“？”）
下一篇将讨论国家字符集的转换。
让实验说话：
SQL> create cluster test1.C_T1 ( c_a number(10));
Cluster created.
SQL> create index test1.c_t1_idx on cluster test1.c_t1;
Index created.SQL> create table test1.t1 (a varchar(2),b number(10),c varchar2(10)) cluster te
st1.c_t1 (b);
Table created.
SQL> select object_id from dba_objects where object_name=’T1′;
OBJECT_ID
----------
6591
SQL> select col#,name,segcol#,intcol# from col$ where obj#=6591;
COL# NAME SEGCOL# INTCOL#
---------- ---------------------------- ---------- ----------
1 A 2 1
2 B 1 2
3 C 3 3
这里可以看到，intcol#为创建表时的列顺序，col#初始情况下跟intcol#相同。而segcol#表示列在数据段上存储时的顺序。由于这是一个聚簇表，因此在存储时最前的列就是聚簇列。（这里为列B）
SQL> alter table test1.t1 set unused (a);
Table altered.
SQL> select col#,name,segcol#,intcol# from col$ where obj#=6591;
COL# NAME SEGCOL# INTCOL#
---------- ---------------------------- ---------- ----------
0 SYS_C00001_08011917:03:19$ 2 1
1 B 1 2
2 C 3 3
这里可以看到，在将列设为UNUSED之后，COL#变为0，其余的列的COL#重新排序。而此时该列在数据段上并没有被删除掉，因此其SEGCOL#列仍然保持原来的值。
SQL> alter table test1.t1 add ( d varchar2(10));
Table altered.
SQL> select col#,name,segcol#,intcol# from col$ where obj#=6592;
COL# NAME SEGCOL# INTCOL#
---------- ---------------------------- ---------- ----------
0 SYS_C00001_08011917:03:19$ 2 1
1 B 1 2
2 C 3 3
3 D 4 4
SQL> alter table test1.t1 drop(c);
Table altered.
SQL> select col#,name,segcol#,intcol# from col$ where obj#=6592;
COL# NAME SEGCOL# INTCOL#
---------- ---------------------------- ---------- ----------
1 B 1 1
2 D 2 2
删除列后，这三个字段均重新进行了排列。
结论：COL#可以表示该列是否在用（0为UNUSED），SEGCOL#表示各列在数据块上存储时的顺序，INTCOL#表示创建表时各列的定义顺序。
作了一个实验，删除unused column时出现异常情况：
SQL> create table test1.t1 (a number,b number, c number);
Table created.
SQL> alter table test1.t1 set unused (a);
Table altered.
SQL> alter table test1.t1 set unused (b);
Table altered.
SQL> select object_id from dba_objects where object_name=’T1′;
OBJECT_ID
----------
6578
SQL> select col#,segcol#,name,intcol# from col$ where obj#=6578;
COL# SEGCOL# NAME INTCOL#
---------- ---------- ---------------------------- ----------
0 1 SYS_C00001_08011916:08:41$ 1
0 2 SYS_C00002_08011916:08:47$ 2
1 3 C 3
SQL> alter table test1.t1 drop (b);
alter table test1.t1 drop (b)
*
ERROR at line 1:
ORA-00904: "B": invalid identifier
SQL> alter table test1.t1 drop (a);
alter table test1.t1 drop (a)
*
ERROR at line 1:
ORA-00904: "A": invalid identifier
SQL> alter table test1.t1 drop (SYS_C00002_08011916:08:47$);
alter table test1.t1 drop (SYS_C00002_08011916:08:47$)
*
ERROR at line 1:
ORA-00907: missing right parenthesis
SQL> alter table test1.t1 drop ("SYS_C00002_08011916:08:47$");
Table altered.
SQL> select col#,segcol#,name,intcol# from col$ where obj#=6578;
COL# SEGCOL# NAME INTCOL#
---------- ---------- ---------------------------- ----------
1 0 C 0
这里segcol#和intcol#居然为0.
SQL> select * from test1.t1;
no rows selected
SQL> insert into test1.t1 values (1);
1 row created.
SQL> select * from test1.t1;
SQL> select * from test1.t1;
C
----------
SQL> commit;
Commit complete.
SQL> select * from test1.t1;
C
----------
SQL> select * from test1.t1;
C
----------
SQL> insert into test1.t1 values (2);
1 row created.
SQL> commit;
Commit complete.
SQL> select * from test1.t1;
C
----------
SQL> select count(*) from test1.t1;
COUNT(*)
----------
2
虽然可以看到有两行数据，但是查询出的值均为NULL。
将数据块DUMP数据，看到的列数居然是0.：
tab 0, row 0, @0xf95
tl: 3 fb: --H-FL-- lb: 0×1 cc: 0
tab 0, row 1, @0xf92
tl: 3 fb: --H-FL-- lb: 0×2 cc: 0
可见此处出现了异常。
而按正常做法则没有什么问题：
SQL> drop table t1;
Table dropped.
SQL> create table test1.t1 (a number,b number,c number, d number);
Table created.
SQL> alter table test1.t1 set unused (a);
Table altered.
SQL> alter table test1.t1 set unused (b);
Table altered.
SQL> alter table test1.t1 drop (d);
Table altered.
SQL> insert into test1.t1 values (1);
1 row created.
SQL> insert into test1.t1 values (2);
1 row created.
SQL> select * from test1.t1;
C
----------
1
2
看起来还是不要删除本身就已经是UNUSED的列。
下面再看看这种做法对已经有数据的表的影响：
SQL> drop table test1.t1;
Table dropped.
SQL> create table test1.t1 as select rownum a,rownum*10 b,rownum*100 c from dba_
objects where rownum<=100;
Table created.
SQL> alter table test1.t1 set unused (b);
Table altered.
SQL> alter table test1.t1 set unused (c);
Table altered.
SQL> select object_id from dba_objects where object_name=’T1′;
OBJECT_ID
----------
6581
SQL> select col#,name,segcol#,intcol# from col$ where obj#=6581;
COL# NAME SEGCOL# INTCOL#
---------- ---------------------------- ---------- ----------
1 A 1 1
0 SYS_C00002_08011916:34:21$ 2 2
0 SYS_C00003_08011916:34:23$ 3 3
SQL> alter table test1.t1 drop ("SYS_C00003_08011916:34:23$");
Table altered.
SQL> select col#,name,segcol#,intcol# from col$ where obj#=6581;
COL# NAME SEGCOL# INTCOL#
---------- ---------------------------- ---------- ----------
1 A 1 1
SQL> select * from test1.t1 where rownum<=10;
A
----------
现在数据仍然是空，出现了异常。因此在9i下强制删除unused column是有问题的。我认为这里一个BUG。不知道10g是不是还是这种情况。
删除unused column的正确方法仍然是：
ALTER TABLE …… DROP UNUSED COLUMNS
下面以例子说话：
SQL> create table test1.unused_test as select rownum a,rownum*2 b,rownum*10 c from dba_objects where rownum<=100;
Table created.
看看数据字典：
SQL> select column_name,data_type,column_id,hidden_column,segment_column_id seg_
cid,internal_column_id internal_cid from dba_tab_cols where owner=’TEST1′ and ta
ble_name=’UNUSED_TEST’;
COLUMN_NAM DATA_TYPE COLUMN_ID HIDDEN SEG_CID INTERNAL_CID
---------- -------------------- --------- ------ ---------- ------------
A NUMBER 1 NO 1 1
B NUMBER 2 NO 2 2
C NUMBER 3 NO 3 3
SQL> select column_name,data_type,column_id from dba_tab_columns where wner=’TE
ST1′ and table_name=’UNUSED_TEST’;
COLUMN_NAM DATA_TYPE COLUMN_ID
---------- -------------------- ---------
A NUMBER 1
B NUMBER 2
C NUMBER 3
SQL> select object_id from dba_objects where wner=’TEST1′ and object_name=’UNUS
ED_TEST’ and object_type=’TABLE’;
OBJECT_ID
----------
6577
SQL> select col#,segcol#,name,intcol# from col$ where obj#=6577;
COL# SEGCOL# NAME INTCOL#
---------- ---------- ---------- ----------
1 1 A 1
2 2 B 2
3 3 C 3
通过DUMP数据库文件块可以看到每行有三列（这里不再列出DUMP文件内容）
下面将B列置为unused状态：
SQL> alter table test1.unused_test set unused (b);
Table altered.
SQL> select column_name,data_type,column_id,hidden_column,segment_column_id seg_
cid,internal_column_id internal_cid from dba_tab_cols where wner=’TEST1′ and ta
ble_name=’UNUSED_TEST’;
COLUMN_NAME DATA_T COLUMN_ID HIDDEN SEG_CID INTERNAL_CID
---------------------------- ------ --------- ------ ---------- ------------
A NUMBER 1 NO 1 1
SYS_C00002_08011915:24:34$ NUMBER YES 2 2
C NUMBER 2 NO 3 3
这里原来的B列，其名字为系统自动生成的一列，命名形式为SYS_CNNNNN_YYMMDDHH24:MI:SS$，NNNNN为原来的COLUMN_ID，前面补0补足成5数。hidden已经变为YES，COLUMN_ID为空。其他两列A和C的COLUMN_ID顺序作了调整。这三列的SEGMENT_COLUMN_ID和INTERNAL_COLUMN_ID没有变化。
SQL> select column_name,data_type,column_id from dba_tab_columns where wner=’TE
ST1′ and table_name=’UNUSED_TEST’;
COLUMN_NAME DATA_T COLUMN_ID
---------------------------- ------ ---------
A NUMBER 1
C NUMBER 2
在DBA_TAB_COLUMNS视图中，B列已经没有显示出来。
SQL> select col#,segcol#,name,intcol# from col$ where obj#=6577;
COL# SEGCOL# NAME INTCOL#
---------- ---------- ---------------------------- ----------
1 1 A 1
0 2 SYS_C00002_08011915:24:34$ 2
2 3 C 3
这里B列的COL#已经变成0.SEGCOL#和INTCOL#列没有变化，NAME也已经变化
DUMP出来的数据中，每一行仍然有三列。
尝试插入数据：
SQL> insert into test1.unused_test values (1234,4321,1);
insert into test1.unused_test values (1234,4321,1)
*
ERROR at line 1:
ORA-00913: too many values
SQL> insert into test1.unused_test values (1234,4321);
1 row created.
SQL> select rowid from test1.unused_test where a=1234 and c=4321;
ROWID
------------------
AAABmxAAFAAAAEuAAA
此ROWID对应的rfile#为5，block#为302，row number为0
DUMP出这一块查看第0行数据，发现在数据块中每行仍然是3列，第二列也就是原来的B列其值为NULL。
现在我们将C列删除：
SQL> alter table test1.unused_test drop (c);
Table altered.
SQL> select column_name,data_type,column_id,hidden_column,segment_column_id seg_
cid,internal_column_id internal_cid from dba_tab_cols where wner=’TEST1′ and ta
ble_name=’UNUSED_TEST’;
COLUMN_NAME DATA_T COLUMN_ID HIDDEN SEG_CID INTERNAL_CID
---------------------------- ------ --------- ------ ---------- ------------
A NUMBER 1 NO 1 1
这里可以看出B列和C列都已经删除。
SQL> select column_name,data_type,column_id from dba_tab_columns where wner=’TE
ST1′ and table_name=’UNUSED_TEST’;
COLUMN_NAME DATA_T COLUMN_ID
---------------------------- ------ ---------
A NUMBER 1
SQL> select col#,segcol#,name,intcol# from col$ where obj#=6577;
COL# SEGCOL# NAME INTCOL#
---------- ---------- ---------------------------- ----------
1 1 A 1
都可以看出B列和C列已经被删除。从这个实验就可以看出，在删除C时会将UNUSED列一并删除。
DUMP出数据块可以发展，块中每一行只有1列。因此SET UNUSED只是修改了数据字典，速度较多。而将COLUMN DROP掉，不仅修改数据字典，而且修改实际的块数据。如果表比较大，会耗费比较长的时间。
Recent Comments