V$session 视图的小运用
我对这个视图没什么好感，因为这个视图又长又臭，对oracle真是爱并痛恨着。
一、kill session 这个是有些危险的操作如果不小心kill 掉了后台进程那就只能哭了，很可能导致数据库的崩溃或者运行时出现故障。为了确保你所kill 的那个session 确实是你所要kill 掉的。应该通过 V$session 视图来获得足够的信息。
SQL> select sid,serial#,username,type,program
2 from V$session
3 where type != 'BACKGROUND';
SID SERIAL# USERNAME TYPE PROGRAM
---------- ---------- ---------- ---------- -------------------------
1 5 SYS USER sqlplus@zeng (TNS V1-V3)
36 10 HR USER sqlplus@zeng (TNS V1-V3)
我通过使用上面的查询就足够让我确定我要kill 掉的会话了。在kill 掉sid = 36 的会话之前我在这个会话中执行如下的update 语句。
SQL> select salary from employees where employee_id = 100;
SALARY
----------
24000
SQL> update employees
2 set salary = salary + 100
3 where employee_id = 100;
1 row updated.
下面把sid = 36,serial# = 10 的会话kill 掉。接着马上查询V$session 视图。
SQL> alter system kill session '36,10';
System altered.
SQL> select sid,serial#,username,type,status,program
2 from V$session
3 where type != 'BACKGROUND';
SID SERIAL# USERNAME TYPE STATUS PROGRAM
---------- ---------- ---------- ---------- -------- -------------------------
1 5 SYS USER ACTIVE sqlplus@zeng (TNS V1-V3)
36 10 HR USER KILLED sqlplus@zeng (TNS V1-V3)
实际上当发出Kill 命令的时候并不是马上的kill 掉某个 session ,而是要等会话中的操作的完成（等待数据库的响应或者事务的回滚）由会话“自杀”。所以发出kill 命令会马上把指定的那个sesion 标记成 killed 但是session 不一定马上就会被kill 掉。再来看看hr 那个会话，在其中随便的执行一条SQL 命令,会报如下的错误。虽然session 已经被
kill 掉了,但是要是保持着一个会话的“尊严”,并不退出sqlplus 。把hr 再次连入数据库，执行下面的查询，我们可以知道被kill 掉的会话中没有提交的事务，oracle 自动的把他回滚了。
SQL> select count(*) from employees;
select count(*) from employees
*
ERROR at line 1:
ORA-00028: your session has been killed
SQL> conn hr/hr
Connected.
SQL> select salary from employees where employee_id = 100;
SALARY
----------
24000
二、会话阻塞的查询。
现在来确定下数据库中用户会话的状态。把sid = 36 的这个hr 的会话标记为session 1.
SQL> select sid,serial#,username,type,program
2 from V$session
3 where type != 'BACKGROUND';
SID SERIAL# USERNAME TYPE PROGRAM
---------- ---------- ---------- ---------- -------------------------
1 5 SYS USER sqlplus@zeng (TNS V1-V3)
36 14 HR USER sqlplus@zeng (TNS V1-V3)
在新增一个hr 用户的会话。把sid = 32 的这个hr 的会话标记为session 2.
SQL> select sid,serial#,username,type,program
2 from V$session
3 where type != 'BACKGROUND';
SID SERIAL# USERNAME TYPE PROGRAM
---------- ---------- ---------- ---------- -------------------------
1 5 SYS USER sqlplus@zeng (TNS V1-V3)
32 23 HR USER sqlplus@zeng (TNS V1-V3)
36 14 HR USER sqlplus@zeng (TNS V1-V3)
在session 1 中执行如下的update 语句。
SQL> update employees
2 set salary = salary + 100
3 where employee_id = 100;
1 row updated.
在session 2 中执行同样的update 语句。现在来确定这个两个会话的相关信息。
SQL> set linesize 200
SQL> select sid,serial#,username,type,
2 blocking_session_status,blocking_session,blocking_instance
3 from V$session
4 where sid in (32,36);
SID SERIAL# USERNAME TYPE BLOCKING_SE BLOCKING_SESSION BLOCKING_INSTANCE
---------- ---------- ---------- ---------- ----------- ---------------- -----------------
32 23 HR USER VALID 36 1
36 14 HR USER NO HOLDER
我们可以知道session 1 也就是sid = 36 的会话把session 也就是sid = 32 的会话阻塞了。更加本质的原因是锁的问题。
SQL> select sid,type,lmode,request,block
2 from V$lock
3 where sid in (32,36);
SID TY LMODE REQUEST BLOCK
---------- -- ---------- ---------- ----------
36 AE 4 0 0
32 AE 4 0 0
32 TX 0 6 0
32 TM 3 0 0
36 TM 3 0 0
36 TX 6 0 1
6 rows selected.
我们从查询可以知道sid = 32 的会话请求一个 lmode 为 6 的锁，但是该锁已经被sid = 36 的会话持有了。所以 sid = 32 的会话被阻塞了。如果想知道会话究竟是因为执行了什么语句而阻塞的可以通过下面的查询获知。
SQL> select sql_text from V$sqltext t1
2 where t1.hash_value = ( select sql_hash_value
3 from V$session t2
4 where t2.sid = 32);
SQL_TEXT
----------------------------------------------------------------
update employees set salary = salary + 100 where employee_id = 100