使用SQLNET.EXPIRE_TIME清除僵死连接(二)
后添加SQLNET.EXPIRE_TIME项
[oracle@orasrv admin]$ more sqlnet.ora
sqlnet.expire_time = 1 #仅仅需要配置此项，后面的各项仅仅是为了生成跟踪日志，可省略
TRACE_LEVEL_SERVER = 16
TRACE_FILE_SERVER = SERVER
TRACE_DIRECTORY_SERVER= /u01/app/oracle/network/trace
TRACE_TIMESTAMP_ SERVER = ON
TRACE_UNIQUE_SERVER = ON
DIAG_ADR_ENABLED=OFF
4、模拟及测试DCD连接
[sql]
C:\Users\robinson.cheng>sqlplus scott/tiger@ora11g --->从windows客户端发起连接
SQL*Plus: Release 11.2.0.1.0 Production on Tue Jun 25 09:57:59 2013
Copyright (c) 1982, 2010,
Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
---Issued the sql to hold a lock
SQL> update emp set sal=sal*1.1 where deptno=20;
5 rows updated.
--disabled the network adapter in VM setting
SQL> select * from dual;
select * from dual
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 29522
Session ID: 15 Serial number: 447
--服务器端环境
SQL> select * from v$version where rownum<2;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
--在服务器端查看session的情况，SCOTT用户的session状态为INACTIVE
SQL> @comm_sess_users;
+----------------------------------------------------+
| User Sessions (All) |
+----------------------------------------------------+
Instance SID Serial ID Status Oracle User O/S User O/S PID Session Program Terminal Machine
--------- ------ --------- --------- ----------- ------------ ------- -------------------------- ---------- -------------
ora11g 15 447 INACTIVE SCOTT Robinson.Che 29522 sqlplus.exe PC39 TRADESZ\PC39
125 5 INACTIVE SYS oracle 4734 sqlplus@orasrv.com (TNS V1 pts/0 orasrv.com
139 9 ACTIVE SYS oracle 29447 sqlplus@orasrv.com (TNS V1 pts/4 orasrv.com
--Get the spid for user scott by SID
SQL> @my_spid_from_sid
Enter value for input_sid: 15
old 4: AND s.sid = &input_sid
new 4: AND s.sid = 15
SID SERIAL# SPID
------ ---------- ------------------------
15 447 29522
--To find the locked object
SQL> @lock_obj
OBJECT_NAME||''||LOCKED_MODE||''||CTIME||''||C.SID||''||SERIAL#
------------------------------------------------------------------
EMP 3 14 15 447
EMP 3 83 15 447
--The trace file exists
SQL> ho ls -hltr /u01/app/oracle/network/trace/s*29522*
-rw-r----- 1 oracle oinstall 241K Jun 25 09:59 /u01/app/oracle/network/trace/server_29522.trc
--->try to issue another sql. the sql is blocked
SQL> set time on;
10:03:46 SQL> delete scott.emp where deptno=20;
delete scott.emp where deptno=20
*
ERROR at line 1:
ORA-01013: user requested cancel of current operation
--Check the server process for scott
10:04:37 SQL> ho ps -ef | grep 29522 | grep -v grep
oracle 29522 1 0 09:58 00:00:00 oracleora11g (LOCAL=NO)
--Could not rea