oracle view lock and session execution sql (summary sharing)

This article brings you relevant knowledge about Oracle, which mainly introduces the issues related to checking locks and sql in session execution. Let’s take a look at it together. I hope it will be helpful to everyone. .

Recommended tutorial: "Oracle Video Tutorial"

The database environment for the test data in this article: Oracle 11g

Why is it said that it is the sql during session execution? It seems that the sql execution record of a certain session cannot be obtained. I have also read a lot of blog posts. Many people on the Internet say that the sql_id is associated with the view v$active_session_history and v$sqlarea. You can query the sql execution record of a certain session. After practice, it is found that it does not work (tried through the table dba_hist_active_sess_history also does not work) , the sql_id of some sql is not recorded at all in v$active_session_history, I Try to modify the parameters: control_management_pack_access, and found that I do not have permission. I checked it and found that the parameter value is normal and the parameter database is open. Refer to the blog post: Oracle V$ACTIVE_SESSION_HISTORY Query No Data - wazz_s - Blog Park

I can query the execution record of SQL through the v$sqlarea view, but I cannot find the sessionid that executed the SQL. It would be great if I had this sessionid, so that I can find out who executed the SQL. .

If I want to query the sql that caused the table to be locked, most of the blog posts on the Internet teach this. Get the corresponding prev_sql_addr field value by querying the view v$session, which is recorded as Value A, and then use value A as the query condition value of the view v$sqlarea field address, and then the corresponding SQL record can be queried. As a practice test, you can find the SQL that finds the lock table, but in most cases you cannot get it in a normal production environment. Why? Please see the introduction below.

This article uses an exploratory approach to study. In order to ensure the accuracy of the data, I opened three database sessions, recorded as session1, session2, and session3 respectively. The specific steps are as follows:

1 Create a new test table and test data in session 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 View the session ID of session1

 select userenv('sid') from dual;

You can see that the session ID is 2546

3 In session1, lock a row of the table zxy_table through select for update, as follows:

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

4 In session2, query that the session ID is 2189:

Then update the row in the table zxy_table with the value zxy_name='zxy1' in session2, as follows:

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

Then we saw that the sql has been blocked, as shown below:

5 Then we came to session 3 to view the lock table situation

First check the table v$locked_object

select * from v$locked_object;

You can see that the session id that caused the lock table is 2546, which is the previous session1, and the object_id is 110154. Of course, In the generation environment, you will definitely see more than one record. You have to execute it several times. After executing it n times, you can still see the record, which proves that this record is the record of the lock table.

Pass object_id ：110154 Query the dba4_objects table to query the detailed lock table information

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

## Query the view 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';

# through sessionid: 2546

## Get the value of prev_sql_addr: 000000012E045E28, and then query the view v$sqlarea through the obtained value

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

As you can see from the picture above, the result There is a statement to lock the table, but many blog posts are finished at this step. Is this query really reliable? The answer is unreliable. You can go back to session1 and execute a sql at will, as follows:

 select * from zxy_table;

Then you can go to session3 to execute

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，这就是前文提出的问题的答案。

推荐教程：《Oracle视频教程》

The above is the detailed content of oracle view lock and session execution sql (summary sharing). For more information, please follow other related articles on the PHP Chinese website!