WITH Clause

----------
Forwarded message ----------
From: XIE WEN-MFK346
<wenxie at motorola.com>
Date:
2008/10/28
Subject: WITH子句(WITH
Clause)
To: xiewenxiewen at gmail.com

也叫子查询分解(subquery
factoring)子句，或公用表表达式(common
table expression)

是SQL99的标准, Oracle
9i开始支持

在一个复杂查询中，如果同样的查询块被调用了多次，就可以考虑使用WITH子句

WITH子句定义了子查询的别名，你能够在查询中引用这个别名多次，这样提高了SQL语句的可读性，有时也能提高运行效能

语法是:

WITH

别名1
AS (子查询1)

别名2
AS (子查询2)

...

SELECT ...

优化器对WITH子句有两种处理:

a.WITH子句被当成内嵌视图(inline
view)处理

语句中别名的部分被替换成子查询，SELECT语句被扩展成带有子查询的语句，然后运行

b.或实例化(materialize)，即建立临时表(temporary
table)

一般，如果别名被调用了多次，Oracle会创建全局临时表(global
temporary table)用于保存子查询结果，因为子查询不会被计算多次，也就提高了查询性能

举例

创建测试表

create table t1 as
select * from all_objects;
create table t2 as select * from
dba_objects;
analyze table t1 compute statistics for table for all
indexes for all indexed columns;
analyze table t2 compute
statistics for table for all indexes for all indexed columns;

用传统SQL语句查询

set pages 9999
line 140
set autot on
select count(*)
from t1,
(select distinct owner username from t1) owners
where
t1.owner = owners.username
union all
select count(*)

from t2, (select distinct owner username from t1) owners
where
t2.owner = owners.username
/

T1全表扫描了两次，计算distinct
owner

改写成用WITH子句查询

with
owners
as (select distinct owner username from t1)
select count(*) from
t1, owners where t1.owner = owners.username
union all
select
count(*) from t2, owners where t2.owner = owners.username
/

执行计划里出现TEMP
TABLE TRANSFORMATION表明因为子查询调用了两次，所以系统自动为子查询建立了临时表,表名是SYS_TEMP_....

统计信息里consistent
gets+physical reads比不使用WITH子句少，读的次数减少了，说明子查询只计算了一次，对性能提高是有一定效果的

还产生了744的redo
size，主要是因为建立了临时表

可以用优化器提示(hint)强制使用内嵌视图或临时表

加上inline提示使用内嵌视图

set autot off

explain plan for

with
owners
as (select /*+inline*/ distinct owner username from t1)
select
count(*) from t1, owners where t1.owner = owners.username
union
all
select count(*) from t2, owners where t2.owner =
owners.username
/
select * from table (dbms_xplan.display);

加上materialize提示使用临时表

explain plan
for
with
owners as (select /*+materialize*/ distinct
owner username from t1)
select count(*) from t1, owners where
t1.owner = owners.username
/
select * from table
(dbms_xplan.display);

inline,materialize提示在正式文档中没有讲，有时也不一定管用，所以一般不建议使用

WITH子句使用上有一些限制

1.不允许WITH嵌套使用

with
outer_subquery as (
with nested_subquery as (select sysdate
as date_column from dual))
select date_column from outer_subquery;

WITH子句中的子查询里不能再用WITH

但可以用在后面其它别名中

with

subquery1 as (select sysdate as date_column from dual),

subquery2 as (select date_column from subquery1)
select
date_column from subquery2;

2.如果定义了子查询而没有用到，就会出错

with
unused_subquery
as (select dummy from dual)
select sysdate from dual;

必须用，不用都不行

外部链接:

subquery_factoring_clause

difference
between sql with clause and inline
subquery
factoring in oracle 9i
Subquery
Factoring (2) 或
2

Xie Wen (谢文)

Network &
Operations,
Multimedia Applications & Services (MDB)
MOTOROLA Inc.
NO.104 mail box,
8th floor, Motorola Tower,
No.
1 Wang Jing East Road, Chao Yang District,
Beijing 100102 P. R.
China
e-mail wenxie at motorola.com

disallow user to change password

----------
Forwarded message ----------
From: XIE WEN-MFK346
<wenxie at motorola.com>
Date:
2008/10/23
Subject: 禁止用户修改自己的密码
To:
xiewenxiewen at gmail.com















使用密码校验函数实现

















建一个用户
conn
/ as sysdba
create user b identified by b;
grant
connect,resource to b;












用户可以随便修改自己的密码
conn
b/b
alter user b identified by bb;
conn b/bb












建一个密码校验函数
conn
/ as sysdba





create or replace
function pwd_verify_func (
username varchar2,

password varchar2,
old_password varchar2
) return
boolean
is
begin
if user in ('SYS','SYSTEM') then

return true;
else
return false;

end if;
end;
/






只允许SYS,SYSTEM用户修改密码






建用户概要文件(profile)
create
profile pwd_profile limit password_verify_function
pwd_verify_func;
alter user b profile pwd_profile;












用户企图修改自己的密码
conn
b/bb
alter user b identified by b;











报错ORA-28221





!oerr ora 28221











必须执行REPLACE语句





alter user b
identified by b replace bb;











用户不能修改自己的密码了











用SYS用户可以修改
conn
/ as sysdba
alter user b identified by b;












修改一下函数中的提示信息





create or replace
function pwd_verify_func (
username varchar2,

password varchar2,
old_password varchar2
) return
boolean
is
begin
if user in ('SYS','SYSTEM') then

return true;
else

raise_application_error(-20001, 'You are not allowed to change the
password');
end if;
end;
/












conn b/b





alter user b
identified by bb replace b;























参考:







Password
Complexity Verification
@?/rdbms/admin/utlpwdmg.sql




























Xie Wen (谢文)




Network &

Operations,

Multimedia Applications & Services (MDB)

MOTOROLA Inc.

NO.104 mail box,

8th floor, Motorola Tower,

No.

1 Wang Jing East Road, Chao Yang District,

Beijing 100102 P. R.

China

e-mail wenxie at motorola.com








或者使用数据库级的触发器
Stopping a user from changing his own Oracle database password



-fin-

case expression and case statement - case表达式和case语句

---------- Forwarded message ----------
From: XIE WEN-MFK346 <wenxie at motorola.com>
Date: 2008/10/21
Subject: case表达式(case expression)和case语句(case statement)
To: xiewenxiewen at gmail.com


case在ANSI标准(SQL92)中被制定，是一种多条件选择的分支结构，也可以用if-then-else结构来实现，但是case比if语句更加简洁，最佳实践(best practice)建议使用case

case分为case表达式(expression)和case语句(statement)两种，再继续划分为simple case和serached case两类

最初8i(8.1.6)的sql语句中支持了case表达式, 到了9i，pl/sql可以同时支持case表达式和case语句



1.case语句(case statement)

case语句是用于pl/sql中的多条件选择的分支结构，对多个条件做判断，然后执行相应的语句
分为simple case语句和searched case语句两种

a.simple case语句
语法:
CASE expression
WHEN result1 THEN
statement;
statement;
...
WHEN result2 THEN
statement;
statement;
...
ELSE
statement;
statement;
...
END CASE;
首先计算expression，然后跟result比较，如果相同则执行后面的statement，不再继续比较，如果不同则继续比较


举例:
set serveroutput on size unlimited
DECLARE
grade CHAR(1);
BEGIN
grade := 'B';
CASE grade
WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
END
CASE;
END;
/


ELSE子句是可选的，如果没写ELSE子句oracle会隐含的加上ELSE RAISE CASE_NOT_FOUND语句，当所有WHEN子句都不满足条件时，就抛出CASE_NOT_FOUND异常

DECLARE
grade CHAR(1);
BEGIN
grade := 'Z';
CASE grade
WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
END CASE;
END;
/


捕获CASE_NOT_FOUND异常
DECLARE
grade CHAR(1);
BEGIN
grade := 'Z';
CASE grade
WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
END CASE;
EXCEPTION
WHEN CASE_NOT_FOUND THEN
DBMS_OUTPUT.PUT_LINE('No such grade');
END;
/



b.searched case语句
语法:
CASE
WHEN expression1 THEN
statement;
statement;
...
WHEN expression2 THEN
statement;
statement;
...
ELSE
statement;
statement;
...
END CASE;

跟simple case一样，首先计算expression的值是真或是假，如果是真则执行后面的statement，不再计算后面的expression，如果是假则继续计算

DECLARE
grade CHAR(1);
BEGIN
grade := 'B';
CASE
WHEN grade = 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
WHEN grade = 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
WHEN grade = 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
WHEN grade = 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
WHEN grade = 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
END
CASE;
END;
-- rather than using the ELSE in the CASE, could use the following
-- EXCEPTION
-- WHEN CASE_NOT_FOUND THEN
-- DBMS_OUTPUT.PUT_LINE('No such grade');
/



2.case表达式(case expression)

case表达式跟case语句差不多，但它返回的是一个值，case表达式可以用于sql语句中，pl/sql赋值语句等

case表达式可以在一条sql语句中完成if-then-else逻辑结构，从而无需使用复杂的pl/sql，跟decode()作用类似，语义更清晰扩展性更好功能更强

case表达式也分为2种, simple case表达式和searched case表达式

a.simple case表达式
语法是:
CASE expression
WHEN result1 THEN return_expresssion1
WHEN result2 THEN return_expresssion2
...
ELSE else_expression
END

注意，case表达式是以END结尾的，case语句是以END CASE结尾的

举例:
DECLARE
grade CHAR(1) := 'B';
appraisal VARCHAR2(20);
BEGIN
appraisal :=
CASE grade
WHEN 'A' THEN 'Excellent'
WHEN 'B' THEN 'Very Good'
WHEN 'C' THEN 'Good'
WHEN 'D' THEN 'Fair'
WHEN 'F' THEN 'Poor'
ELSE 'No such grade'
END;
DBMS_OUTPUT.PUT_LINE('Grade ' || grade || ' is ' || appraisal);
END;
/


在sql语句中使用case表达式
set pages 9999 line 140
col object_name for a30
select object_name,
case object_type
when 'TABLE' then 'is a table'
when 'INDEX' then 'is a index'
else 'is not a table or index'
end
from user_objects
/


跟case语句不同，如果没有ELSE子句，且不满足任何条件，返回的是空值(NULL)，不会抛出异常
DECLARE
grade CHAR(1) := 'Z';
appraisal VARCHAR2(20);
BEGIN
appraisal :=
CASE grade
WHEN 'A' THEN 'Excellent'
WHEN 'B' THEN 'Very Good'
WHEN 'C' THEN 'Good'
WHEN 'D' THEN 'Fair'
WHEN 'F' THEN 'Poor'
END;
DBMS_OUTPUT.PUT_LINE('Grade ' || grade || ' is ' || NVL(appraisal,'Null!'));
END;
/


THEN或ELSE后面返回的结果不能都是空(You cannot specify the literal NULL for every return_expr and the else_expr.)

DECLARE
grade CHAR(1) := 'B';
appraisal VARCHAR2(20);
BEGIN
appraisal :=
CASE grade
WHEN 'A' THEN NULL
WHEN 'B' THEN NULL
WHEN 'C' THEN NULL
WHEN 'D' THEN NULL
WHEN 'F' THEN NULL
END;
DBMS_OUTPUT.PUT_LINE('Grade ' || grade || ' is ' || appraisal);
END;
/


但是，用在sql语句里就可以都是空，不知道为什么？
select object_name,
case object_type
when 'TABLE' then NULL
when 'INDEX' then NULL
end
from user_objects;


b.searched case表达式
语法是:
CASE
WHEN expression1 THEN return_expresssion1
WHEN expression2 THEN return_expresssion2
...
ELSE else_expression
END

DECLARE
grade CHAR(1) := 'B';
appraisal VARCHAR2(120);
id NUMBER := 8429862;
attendance NUMBER := 150;
min_days CONSTANT NUMBER := 200;
FUNCTION attends_this_school(id NUMBER) RETURN BOOLEAN IS
BEGIN RETURN TRUE; END;
BEGIN
appraisal :=
CASE
WHEN attends_this_school(id) = FALSE THEN 'N/A - Student not enrolled'
-- Have to test this condition early to detect good students with bad attendance
WHEN grade = 'F' OR attendance < min_days THEN 'Poor (poor performance or bad attendance)'
WHEN grade = 'A' THEN 'Excellent'
WHEN grade = 'B' THEN 'Very Good'
WHEN grade = 'C' THEN 'Good'
WHEN grade = 'D' THEN 'Fair'
ELSE 'No such grade'
END;
DBMS_OUTPUT.PUT_LINE('Result for student ' || id || ' is ' || appraisal);
END;
/


用于sql语句
select max(case
when object_type not in ('TABLE','INDEX') then sysdate
else created
end)
from user_objects;


使用case将多条语句合并成一条
比如要统计object_id<16000,<20000和>=20000的数量，只用一条语句，只扫描了一次性能得到了优化
select count(case
when object_id<16000 then 1 end) count1,
count(case when object_id>=16000 and object_id<20000 then 1
end) count2,
count(case
when object_id>=20000 then 1 end) count3
from user_objects;



练习题：
1. 运行如下存储过程，得到什么结果
CREATE OR REPLACE FUNCTION fruit_translator (letter_in IN VARCHAR2) RETURN VARCHAR2
IS
retval VARCHAR2 (100);
BEGIN
RETURN CASE
WHEN letter_in = 'A' THEN 'Apple'
WHEN letter_in = 'B' THEN 'Banana'
END;
END
fruit_translator;
/
SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE ('Expression good for you = ' || NVL (fruit_translator ('C'), 'Unknown Fruit'));
END;
/

2. 运行如下存储过程，得到什么结果
CREATE OR REPLACE FUNCTION fruit_translator (letter_in IN VARCHAR2) RETURN VARCHAR2
IS
retval VARCHAR2(100);
BEGIN
CASE
WHEN letter_in = 'A' THEN retval := 'Apple';
WHEN letter_in = 'B' THEN retval := 'Banana';
END CASE;
RETURN retval;
END fruit_translator;
/
SET SERVEROUTPUT ON
BEGIN
DBMS_OUTPUT.PUT_LINE ('Statement good for you = ' || NVL (fruit_translator ('C'), 'Unknown Fruit'));
END;
/

3. 下面用到的CASE是simple case还是searched case?
DECLARE
salary NUMBER := 20000;
employee_id NUMBER := 36325;
PROCEDURE give_bonus (emp_id IN NUMBER, bonus_amt IN NUMBER) IS
BEGIN
DBMS_OUTPUT.PUT_LINE(emp_id);
DBMS_OUTPUT.PUT_LINE(bonus_amt);
END;
BEGIN
CASE TRUE
WHEN salary >= 10000 AND salary <=20000 THEN give_bonus(employee_id, 1500);
WHEN salary > 20000 AND salary <= 40000 THEN give_bonus(employee_id, 1000);
WHEN salary > 40000 THEN give_bonus(employee_id, 500);
ELSE give_bonus(employee_id, 0);
END
CASE;
END;
/




参考：
CASE Expressions
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/expressions004.htm#sthref2637

CASE Statement
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/case_statement.htm#LNPLS01304

CASE Expressions
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/fundamentals.htm#sthref520

Using CASE Statements
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14261/controlstructures.htm#sthref912

Combine Multiples Scans with CASE Statements
http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/sql_1016.htm#i26629

Oracle PL/SQL Programming By Steven Feuerstein, Bill Pribyl
http://books.google.com/books?id=Sqe-GHk_VJ0C&source=gbs_toc_r&cad=0_0&pg=PA92

What you Ought to Know About CASE in Oracle PL/SQL
http://awads.net/wp/2007/07/25/what-you-ought-to-know-about-case-in-oracle-plsql/




Xie Wen (谢文)
Network & Operations,
Multimedia Applications & Services (MDB)
MOTOROLA Inc.
NO.104 mail box, 8th floor, Motorola Tower,
No. 1 Wang Jing East Road, Chao Yang District,
Beijing 100102 P. R. China
e-mail wenxie at motorola.com




-fin-

time sync with NTP - 配置NTP(网络时间协议)同步时间

---------- Forwarded message ----------
From: XIE WEN-MFK346 <wenxie at motorola.com>
Date: 2008/10/16
Subject: ntp同步时间
To: xiewenxiewen at gmail.com

网络时间协议NTP(Network Time Protocol)是用于校正网络上服务器的时间的协议
客户端通过UDP的123端口接连提供NTP时间服务的服务器，同步时间，保证客户端的时间是准确的
NTP采用客户机-服务器的分层结构
最高一级是第0级(stratum 0)，比如美国海军天文台的铯原子钟(cesium clock)或全球定位系统(GPS)时钟，也叫参考时钟(reference clocks)
往下一级是第1级(stratum 1)，从第0级的参考时钟同步时间，是在因特网上提供时间服务的顶级时间服务器
再往下一级是第2级(stratum 2),从第1级同步时间，这一级服务器在因特网上有很多，所以一般就从这级同步时间
以此类推，下一级从上一级同步时间。最低一级是16级(stratum 16)，表示服务不可访问

ntp.org上有个项目叫pool.ntp.org，是按大洲国家划分建立起一系列虚拟的NTP服务器集群，用来分散单个服务器上的压力
选用了pool做时间服务器，它会按一定规则分配一个真实的服务器地址返回给客户机
pool.ntp.org是最高一级，包括了全部服务器
往下按洲划分为europe,north-america,oceania,asia,south-america,africa
比如, europe.pool.ntp.org, africa.pool.ntp.org
再往下按国家分，比如cn.pool.ntp.org
每级域名下又分3个下级域名,从0到2
比如0.pool.ntp.org,1.asia.pool.ntp.org,0.cn.pool.ntp.org,2.us.pool.ntp.org

NTP的工作方式有下面三种:
客户端-服务器(client-server):客户机从服务器同步时间
点对点(peer-to-peer):互相参考校正时间，用于配置多个时间服务器给客户使用，保证可靠性
广播/多播(broadcast/multicast):用于局域网内服务器向客户机定时广播时间，精度不高

举例

图1

 ---------------------   ----------------------------   ---------------------
| 0.asia.pool.ntp.org | | north-america.pool.ntp.org | | 1.asia.pool.ntp.org |
 ---------------------   ----------------------------   ---------------------
  ^                           ^                               ^
  |                           |                               |
  | 
   --------------------- 
|
|
| 
|
| |
---------------------------------------------------

| | |
| |
| 
-----------------
| |
| 
| cn.pool.ntp.org
|
| |
| 
-----------------
| |
| 
^
| | | 
--------------------- | 
----------------------
| |
| | europe.pool.ntp.org
| | | oceania.pool.ntp.org
|
| | | 
--------------------- | 
----------------------
| |
| 
^ 
| 
^
| | | 
| 
| 
|
| | | 
--------------- | -----------

| | | 
| | |
| |
| 
| | |
| |
| 
| | |
| |
| 
| | | 
Internet
----------------------------------------------------------------------------------------------

| | | 
| | | 
Intranet
| |
| 
| | |
| |
| 
| | |

--------------- 
----------------
| time1 
| peer 
| time2 |

| 192.168.11.16
|<----------------->| 192.168.12.100
|
--------------- 
----------------
^ ^ 
^ 
^ ^ ^
|
| | 
| | |
| 
-------- --------------------|--- 
|
| 
| | | 
| |
|
--------------------------------- 
|
| | |
| | 
|
| | 
---|--|---------- --------------

| | |
| | |

| | |
| | |

------- ------- 
-------
| srv 1 | | srv 2 | .... | srv n |

------- ------- 
-------

内网上有两台机器time1,time2，从公网上时间服务器上同步时间，用的是服务器/客户机模式
time1,time2采用点对点对等的模式，互相同步
time1,time2向内网上其它机器提供时间同步服务，用的也是服务器/客户机模式
本来想在内网内用广播模式的，但路由器好像阻止了广播，所以没成功

在time2上配置/etc/ntp.conf

restrict是安全访问控制用的
restrict default ignore 默认阻止使用NTP服务
restrict 127.0.0.1 允许本地127.0.0.1访问NTP服务。后面什么都不加，表示有全部的权限
restrict 192.168.11.0 mask 255.255.255.0 nomodify notrp 允许192.168.11.0子网访问服务，但不能修改服务器上时间(nomodify)或查看事件日志(notrap)，可以查询
server 指定了时间服务器地址
后面的restrict指定时间服务器的访问控制权限。我不知道应该用什么权限好，所以全部开放了

server 127.127.1.0
fudge 127.127.1.0 stratum 10
在本地建立一个假的时间服务器

broadcast 是广播地址，暂时没用
broadcastdelay 广播延迟
driftfile 保存了系统时钟频率偏差。用于补偿时钟漂移，保证精确度
keys 口令文件，服务器之间认证才用到，暂时没用

配置/etc/ntp/step-tickers
脚本/etc/init.d/ntpd中，在每次ntpd启动前，首先调用ntpdate从这些服务器同步时间
配置成跟/etc/ntp.conf的server一样就行
awk '/^server/{print $2}' /etc/ntp.conf |grep -v '127.127.1.0' />/etc/ntp/step-tickers


配置/etc/ntp/ntpservers
不知道干什么的，redhat说的:
#This file contains a list of ntp servers to show in the system-config-date user
interface.
#It is not recommended that you modify this file by hand.
配成跟step-tickers一样
awk '/^server/{print $2}' /etc/ntp.conf |grep -v '127.127.1.0' />/etc/ntp/ntpservers


启动ntpd服务
service ntpd start


这时时间还没有同步，要等几分钟后NTP才能正常工作
ntpstat


ntpq -p查看


第1列remote 是上级服务器地址, 前三行是pool.ntp.org上的，LOCAL(0)是本地的, MGT是点对点的那个
第2列refid, 表示上上级服务器的地址, INIT表示正在初始化, BCST表示是广播地址, RSRT表示不可访问
第3列st，表示服务器的级别(stratum), 16说明当前不可访问
其它略

10分钟后,


一般在reach值30多以后，看到同步时间服务正常了

配置time1，方法一样





配置客户机 time1,time2作时间服务器




其它时间同步方式:
ntpdate 前台命令方式，放在crontab定时调用，也是NTP协议的，没ntpd好用，不建议使用
sntp 简单的NTP协议，精度秒级
rdate 不同于NTP协议，精度只有秒级


参考:
Introduction to NTP
The NTP FAQ and HOWTO
A complex NTP configuration scenario
NTP 時間伺服器

ntp.org www.ntp.org
ntp作者 http://www.eecis.udel.edu/~mills/ntp/
RFC1305 http://www.ietf.org/rfc/rfc1305.txt




Xie Wen (谢文)
Network & Operations,
Multimedia Applications & Services (MDB)
MOTOROLA Inc.
NO.104 mail box, 8th floor, Motorola Tower,
No. 1 Wang Jing East Road, Chao Yang District,
Beijing 100102 P. R. China
e-mail wenxie at motorola.com




--fin-

inbound connect timout - INBOUND_CONNECT_TIMEOUT参数

---------- Forwarded message ----------
From: XIE WEN-MFK346 <wenxie at motorola.com>
Date: 2008/10/13
Subject: inbound_connect_time参数
To: xiewenxiewen at gmail.com


1. sqlnet.ora 参数 SQLNET.INBOUND_CONNECT_TIMEOUT
表示等待用户认证超时的时间，单位是秒，缺省值是60秒
如果用户认证超时了，服务器日志alert.log显示出错信息
"WARNING: inbound connection timed out (ORA-3136)"

举例：
连接监听器，不输入用户名/密码
sqlplus /@test

[oracle@MGT ~]$ sqlplus /@test

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jan 20 07:53:42 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:

等一分钟后,alert.log报ora-3136错

Tue Jan 20 07:54:42 2009
WARNING: inbound connection timed out (ORA-3136)

编辑$ORACLE_HOME/network/admin/sqlnet.ora，设置参数SQLNET.INBOUND_CONNECT_TIMEOUT等于12秒
grep SQLNET.INBOUND_CONNECT_TIMEOUT $ORACLE_HOME/network/admin/sqlnet.ora

[oracle@MGT ~]$ grep SQLNET.INBOUND_CONNECT_TIMEOUT $ORACLE_HOME/network/admin/sqlnet.ora
SQLNET.INBOUND_CONNECT_TIMEOUT=12
[oracle@MGT ~]$

再连接监听器，不输入用户名/密码
sqlplus /@test

[oracle@MGT ~]$ sqlplus /@test

SQL*Plus: Release 10.2.0.4.0 - Production on Tue Jan 20 07:59:39 2009

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

ERROR:
ORA-01017: invalid username/password; logon denied


Enter user-name:

过了12秒，alert.log又报错了

Tue Jan 20 07:59:51 2009
WARNING: inbound connection timed out (ORA-3136)

这个参数只要修改了sqlnet.ora配置文件，便立即生效，不需要重启监听器/数据库


2. listener.ora的参数 inbound_connect_timeout_监听器名
表示等待用户连接请求超时的时间，单位是秒，缺省值是60秒
如果连接请求超时了，监听器日志listener.log显示出错信息
"TNS-12525: TNS:listener has not received client's request in time allowed"
服务器日志alert.log里不报错

举例:
用telnet命令在监听器端口建立连接
time telnet localhost 1521
过一分钟后，连接被断开

[oracle@MGT ~]$ time telnet localhost 1521
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
Connection closed by foreign host.

real 1m0.015s
user 0m0.001s
sys 0m0.002s
[oracle@MGT ~]$

同时监听器日志报错

20-JAN-2009 08:01:29 * 12502
TNS-12502: TNS:listener received no CONNECT_DATA from client
20-JAN-2009 08:02:32 * * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=3871)) * establish * * 12525
TNS-12525: TNS:listener has not received client's request in time allowed
TNS-12535: TNS:operation timed out
TNS-12606: TNS: Application timeout occurred

把参数修改成9秒
lsnrctl set inbound_connect_timeout 9

[oracle@MGT ~]$ lsnrctl set inbound_connect_timeout 9

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 20-JAN-2009 08:03:06

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
LISTENER parameter "inbound_connect_timeout" set to 9
The command completed successfully
[oracle@MGT ~]$

再试
time telnet localhost 1521

[oracle@MGT ~]$ time telnet localhost 1521
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
Connection closed by foreign host.

real 0m9.004s
user 0m0.000s
sys 0m0.002s
[oracle@MGT ~]$

20-JAN-2009 08:03:28 * * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=3891)) * establish * * 12525
TNS-12525: TNS:listener has not received client's request in time allowed
TNS-12535: TNS:operation timed out
TNS-12606: TNS: Application timeout occurred

连接超时缩短为9秒

这个参数也是立即生效的，不需要重启监听器或数据库

也可以修改listener.ora配置文件，然后reload监听器，使新的参数生效
grep INBOUND_CONNECT_TIMEOUT $ORACLE_HOME/network/admin/listener.ora
lsnrctl reload

[oracle@MGT ~]$ grep INBOUND_CONNECT_TIMEOUT $ORACLE_HOME/network/admin/listener.ora
INBOUND_CONNECT_TIMEOUT_LISTENER=5
[oracle@MGT ~]$ lsnrctl reload

LSNRCTL for Linux: Version 10.2.0.4.0 - Production on 20-JAN-2009 08:05:44

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

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
The command completed successfully
[oracle@MGT ~]$

指定时间后连接超时

[oracle@MGT ~]$ time telnet localhost 1521
Trying 127.0.0.1...
Connected to localhost.
Escape character is '^]'.
Connection closed by foreign host.

real 0m5.006s
user 0m0.002s
sys 0m0.002s
[oracle@MGT ~]$

20-JAN-2009 08:06:06 * * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=3916)) * establish * * 12525
TNS-12525: TNS:listener has not received client's request in time allowed
TNS-12535: TNS:operation timed out
TNS-12606: TNS: Application timeout occurred

外部链接:
ORA-12525
ORA-03136
SQLNET.INBOUND_CONNECT_TIMEOUT
Connections that Used to Work in Oracle 10.1 Now Intermittently Fail with ORA-3113,ORA-3106 or ORA-3136 in 10.2
Troubleshooting ORA - 3136 WARNING Inbound Connection Timed Out
When do SQLNET.ORA changes take effect ?



Xie Wen (谢文)
Network & Operations,
Multimedia Applications & Services (MDB)
MOTOROLA Inc.
NO.104 mail box, 8th floor, Motorola Tower,
No. 1 Wang Jing East Road, Chao Yang District,
Beijing 100102 P. R. China
e-mail wenxie at motorola.com


-fin-

double hyphens in shell - 命令中双连字符的作用

---------- Forwarded message ----------
From: XIE WEN-MFK346 <wenxie at motorola.com>
Date: 2008/10/6
Subject: 命令行中双连字符(double hyphen)的作用
To: xiewenxiewen at gmail.com

是区分选项(options)和参数(parameters)

通常一条命令包含若干选项和参数，如
grep -i --line-number -e "A" 123.txt 456.txt
-i是一个选项
--line-number是一个选项(是长格式的选项,long option)
-e 也是选项,后面"A"也是该选项的一部分
再后面的123.txt和456.txt是两个参数

选项和参数的位置不定，可以随便写
grep -i --line-number -e "A" 123.txt 456.txt

和
grep 123.txt --line-number 456.txt -i -e "A"

是一样的


如果当文件名是以-开头的，会被误认为是个选项，导致错误的结果
grep -i -e "A" -a.txt

文件名为"-a.txt"，被当成选项了

为了避免出错，可以在文件名前加上路径
grep -i -e "A" ./-a.txt


也可以使用双连字符。双连字符的作用是区分选项和参数,在双横线前面的都是选项，后面的都是参数
grep -i -e "A" -- -a.txt


其它别的命令也可以这么用
ls -- -a.txt


ls -l -- -a.txt


cat -n -- -a.txt


grep '--' 123.txt，这种情况是把'--'当成了分隔符，把123.txt当成了pattern，然后等待用户输入文本，所以出错了


解决方法:
加-e选项
grep -e '--' 123.txt


或加双连字符
grep -- '--' 123.txt



Xie Wen (谢文)
Network & Operations,
Multimedia Applications & Services (MDB)
MOTOROLA Inc.
NO.104 mail box, 8th floor, Motorola Tower,
No. 1 Wang Jing East Road, Chao Yang District,
Beijing 100102 P. R. China
e-mail wenxie at motorola.com



-fin-