oracle查看锁及session执行中的sql

由于作者水平有限，有不对的地方请指正

本文测试数据的数据库环境：Oracle 11g

           为什么说是session执行中的sql呢，某个session的sql执行记录好像获取不到，也看了很多的博文，网上很多有说通过视图v$active_session_history和v$sqlarea关联sql_id就能查询到某个session的sql执行记录，经过实践发现是不行的(通过表dba_hist_active_sess_history试过了也是不行)，某些sql的sql_id在v$active_session_history根本就没有记录，我尝试修改参数:control_management_pack_access，发现我没有权限，而且我对了一下，参数值是正常的，该参数数据库是开启的，参考博文：Oracle V$ACTIVE_SESSION_HISTORY查询没有数据 - wazz_s - 博客园

        通过v$sqlarea视图能查询到sql的执行记录，但却查不到执行该sql的sessionid，如果有这个sessionid该多好，我就能查到那个人执行了该sql。

        如果我要查询导致锁表的那一条sql,网上大部分的博文都是这样教的，通过查询视图v$session得到对应的prev_sql_addr字段值，记为值A，然后通过值A作为视图v$sqlarea字段address的查询条件值，然后就可以查询到对应的sql记录了。这种作为练习测试你是可以找到找到锁表的sql，但是在正常生产环境下大部分情况下你是获取不到的，为什么呢，请看下文的介绍。

本文以探索的方式进行学习，为了保证数据的准确性，我开了三个数据库会话，分别记为session1、session2、session3，具体步骤如下：

1  在会话session1中新建测试表及测试数据

--新建测试表
create table zxy_table(zxy_id int,zxy_name varchar2(20));
--插入数据
insert into zxy_table(zxy_id,zxy_name) values(1,'zxy1');
insert into zxy_table(zxy_id,zxy_name) values(2,'zxy2');
insert into zxy_table(zxy_id,zxy_name) values(3,'zxy3');
insert into zxy_table(zxy_id,zxy_name) values(4,'zxy4');
commit;

2  查看session1的会话Id

 select userenv('sid') from dual;

 

可以看到会话Id为2546

3  在session1中，通过select  for update的对表zxy_table的某一行进行锁定，如下：

 select * from zxy_table where zxy_name='zxy1' for update;

4  在session2中，查询到该会话id为2189：

 

然后在session2中对表zxy_table值为zxy_name='zxy1'的行进行update，如下：

update zxy_table set zxy_name='zxy1_modify' where zxy_name='zxy1';

然后看到该sql已经被堵塞了，如下图：

5  然后我们来到会话session3查看锁表的情况了

首先查看表v$locked_object 

select * from v$locked_object;

 可以看到造成锁表的会话id为2546，就是前面的session1，同时object_id为110154，当然咯，在生成环境中，你看到的肯定不止一条记录，你要多执行几遍，执行n遍后，还能看到的记录，证明这条记录就是锁表的记录

通过object_id：110154查询dba4_objects表查询详细锁表的信息

select object_name as 被锁的表名称,obj.* from dba_objects obj where object_id='110154';

 通过sessionid：2546查询视图v$session

select 
       s.prev_sql_addr,
       module as 客户端工具名称,
       s.user# as 数据库账号名,
       s.osuser as 连接数据库客户端对应的window账号名称,
       s.machine as 连接数据库客户端对应的计算机名称,
       s.* 
from v$session s where sid='2546';

 得到prev_sql_addr的值为：000000012E045E28，然后通过得到的值查询视图v$sqlarea

select * from v$sqlarea where address='000000012E045E28';

 从上图中可以看到造成锁表的语句了，但是很多博文到了这一步就完事了，这样查询真的靠谱吗？答案是不靠谱的，你可以回到session1中随便执行一条sql ,如下：

 select * from zxy_table; 

然后你再到session3执行

select 
       s.prev_sql_addr,
       module as 客户端工具名称,
       s.user# as 数据库账号名,
       s.osuser as 连接数据库客户端对应的window账号名称,
       s.machine as 连接数据库客户端对应的计算机名称,
       s.* 
from v$session s where sid='2546';

 再看看prev_sql_addr是不是变了，从000000012E045E28变为了00000001FB03CEC0，再通过00000001FB03CEC0查询视图v$sqlarea

select * from v$sqlarea where address='00000001FB03CEC0';

得到的sql_text是select * from zxy_table，你敢说这条sql导致了锁表吗？所有只能说是session1当前执行的sql，而且你很难保证session1执行完锁表的sql： select * from zxy_table where zxy_name='zxy1' for update且在提交前不再执行别的sql，这就是前文提出的问题的答案。

好了，本文介绍到此结束。五一快过完了，又得收拾行囊回广州上班了。