Home  >  Article  >  Database  >  Introduction to Oracle's method of viewing locked tables and unlocking them (code example)

Introduction to Oracle's method of viewing locked tables and unlocking them (code example)

不言
不言forward
2019-02-12 11:47:063096browse

This article brings you an introduction to Oracle's method of viewing locked tables and unlocking them (code examples). It has certain reference value. Friends in need can refer to it. I hope it will be helpful to you. .

--The following are related tables

SELECT * FROM v$lock;
SELECT * FROM v$sqlarea;
SELECT * FROM v$session;
SELECT * FROM v$process ;
SELECT * FROM v$locked_object;
SELECT * FROM all_objects;
SELECT * FROM v$session_wait;

--View the locked tables

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;

--Check which user and process are deadlocked

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;

--Check the connected process

SELECT sid, serial#, username, osuser FROM v$session;

--3. Find out the sid, serial#,os_user_name, machine_name, terminal of the locked table, the lock type, mode

SELECT s.sid, s.serial#, s.username, s.schemaname, s.osuser, s.process, s.machine,
s.terminal, s.logon_time, l.type
FROM v$session s, v$lock l
WHERE s.sid = l.sid
AND s.username IS NOT NULL
ORDER BY sid;

This statement will find the locks generated by all DML statements in the database, and also It can be found that
Any DML statement actually generates two locks, one is a table lock and the other is a row lock.

--Kill the process sid,serial

#alter system kill session '210,11562'; There must be spaces when connecting here, otherwise an error will be reported, for example(alter system kill session'210,11562';).

The above is the detailed content of Introduction to Oracle's method of viewing locked tables and unlocking them (code example). For more information, please follow other related articles on the PHP Chinese website!

Statement:
This article is reproduced at:cnblogs.com. If there is any infringement, please contact admin@php.cn delete