In depth discussion: how to query the users of the locked table and the method of releasing the locked table in Oracle

  • 2021-07-01 08:28:02
  • OfStack

You can use the following SQL statement in PL/SQL to query which tables are locked in the current database and which users are locking these tables:

SELECT
A. OWNER-User of OBJECT
A.OBJECT_NAME,--OBJECT name (table name)
B.XIDUSN,
B.XIDSLOT,
B.XIDSQN,
B.SESSION_ID--session for the lock table user
B.ORACLE_USERNAME,--The Oracle user name of the lock table user
B.OS_USER_NAME--The operating system login user name of the lock table user
B.PROCESS,
B.LOCKED_MODE,
C. MACHINE--The computer name of the lock table user (for example: WORKGROUP\ UserName)
C. STATUS--Lock table status
C.SERVER,
C.SID,
C.SERIAL#,
C. PROGRAM--Database management tool for lock table users (for example: ob9.exe)
FROM
ALL_OBJECTS A,
V$LOCKED_OBJECT B,
SYS.GV_$SESSION C
WHERE
A.OBJECT_ID = B.OBJECT_ID
AND B.PROCESS = C.PROCESS
ORDER BY 1, 2 You can also use the following command to kill drop the entries of the current lock table:

alter system kill session 'sid, serial#'
--For example: alter system kill session '57, 10325


Related articles: