How to check the reason of lock table in oracle

The reason why oracle locks the table is: when a program performs an insert on a table and has not yet committed, another program also performs an insert on the same table, and the resource is busy at this time. Exception, that is, lock table.

The operating system for this tutorial: Windows 10 system, Oracle version 19c, Dell G3 computer.

What is the reason why Oracle locks the table?

The main reasons for locking the table are:

(1) Program A executes insert on tableA, Before commit, program B also inserts tableA. At this time, a resource busy exception will occur, which is lock table

(2) Lock table often occurs in concurrency rather than parallel (in parallel, a thread When operating the database, another thread cannot operate the database, CPU and I/O allocation principle)

Methods to reduce the probability of locking the table:

(1) Reduce insert, update, and delete statements The time between execution and commit. Specifically, change batch execution to single execution and optimize the non-execution speed of sql itself

(2) Roll back things if there is an exception

The reason for locking the table may be that the row data has been modified and forgotten Submitting will also cause the table to be locked.

1. It is recommended to check the reason before making a decision when locking the table.

1 The code for lock table query has the following form:

select count(*) from v$locked_object;
select * from v$locked_object;

2. Check which table is locked

select b.owner,b.object_name,a.session_id,a.locked_mode from v$locked_object a,dba_objects b where b.object_id = a.object_id;

3 Check which session caused it

select b.username,b.sid,b.serial#,logon_time from v$locked_object a,v$session b where a.session_id = b.sid order by b.logon_time;

4. Check which sql is causing it

select b.username,b.sid,b.serial#,c.* from v$locked_object a,v$session b,v$sql c where a.session_id = b.sid
and b.SQL_ID = c.sql_id and c.sql_id = ''
order by b.logon_time;

5. Kill the corresponding process

Execute the command: alter system kill session'1025,41';

1025 among them is sid, 41 is serial#.

2. The reason why Oracle users are often locked

prompts when logging in: user test is locked

1. Users with the dba role Log in and unlock, first set the specific time format to view the specific time

SQL> alter session set nls_date_format=‘yyyy-mm-dd hh24:mi:ss’;

Session altered.

2. View the specific locked time

SQL> select username,lock_date from dba_users where username='TEST';
USERNAME LOCK_DATE
------------------------------ -------------------
TEST 2021-03-10 08:51:03

3. Unlock

SQL> alter user test account unlock;

User altered.

The above is the detailed content of How to check the reason of lock table in oracle. For more information, please follow other related articles on the PHP Chinese website!