How to query Oracle locked tables

In Oracle, you can use the SELECT statement to query the locked table. This statement is used to simply query data information. The syntax is "select*from v$locked_object a,dba_objects b where b.object_id=a.object_id" .

The operating environment of this tutorial: Windows 10 system, Oracle 11g version, Dell G3 computer.

How to query the locked table in Oracle

1. Query the reason why the table is locked

select l.session_id sid,
       s.serial#,
       l.locked_mode,
       l.oracle_username,
       s.user#,
       l.os_user_name,
       s.machine,
       s.terminal,
       a.sql_text,
       a.action
  from v$sqlarea a, v$session s, v$locked_object l
  where l.session_id = s.sid
  and s.prev_sql_addr = a.address
  order by sid, s.serial#;

2. Query the locked table

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 is causing 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. Kill the corresponding process to unlock

alter system kill session'135,397'

--135 is sid and 397 is serial

#Note: This user must Have DBA authority, otherwise it will prompt that the tables and views cannot be found

Recommended tutorial: "Oracle Video Tutorial"

The above is the detailed content of How to query Oracle locked tables. For more information, please follow other related articles on the PHP Chinese website!