How to delete oracle lock (a brief analysis of the method)

In Oracle database management, locking is a common phenomenon. When a database object is used, the Oracle system marks the object as "locked" to ensure that people do not accidentally change or delete the object. However, sometimes these locks may prevent other programs or users from functioning properly, and the locks need to be removed.

How to delete Oracle lock?

1. Check the locking situation:

You can check the current locking situation of the Oracle database through the following script:

SELECT
   c.owner,c.object_name,c.object_type, b.sid, b.serial#, b.status, b.osuser, b.machine
FROM
   v$locked_object a ,all_objects c ,v$session b
WHERE
   b.sid = a.session_id AND a.object_id = c.object_id;

You can know all the current locks through the query results of the above script. object, as well as the session information that locks the object, and how to perform the unlocking operation can be determined based on this information.

2. Manually unlock:

After confirming that the lock needs to be unlocked, we can manually delete the Oracle lock through the following steps:

① Determine the SID and SERIAL# of the locked session ：

SELECT SID,SERIAL# FROM V$SESSION WHERE AUDSID = USERENV('SESSIONID');

Among them, USERENV('SESSIONID') is to obtain the ID of the current session.

②Use the SID and SERIAL# and KILL statement queried above to terminate the session:

ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;

③. If there are multiple locks, you need to repeat the above steps.

3. Use PL/SQL to unlock the lock

You can use PL/SQL to unlock the lock in Oracle. The specific implementation is as follows:

①.Create a stored procedure

CREATE OR REPLACE PROCEDURE kill_locks_table (p_username IN VARCHAR2, p_table_name IN VARCHAR2) IS
  l_obj_id NUMBER;
  l_sess_id NUMBER;
  l_serial# NUMBER;
  l_locktype VARCHAR2(32);
BEGIN
  SELECT object_id INTO l_obj_id 
    FROM dba_objects 
   WHERE object_name = p_table_name and owner = p_username and object_type in ('TABLE', 'INDEX');

  FOR l_rec IN (SELECT session_id, lock_type, mode_held 
                     FROM v$locked_object 
                    WHERE object_id = l_obj_id) LOOP
    l_sess_id := l_rec.session_id;
    l_locktype := l_rec.lock_type || ' ' || l_rec.mode_held;
    SELECT serial# INTO l_serial# FROM v$session WHERE sid = l_sess_id;
    dbms_output.put_line('Killing session ' || l_sess_id || ' serial# ' || l_serial# || ' which is holding lock: ' || l_locktype);
    EXECUTE IMMEDIATE 'alter system kill session ''' || l_sess_id || ',' || l_serial# || '''';
  END LOOP;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    dbms_output.put_line('No locks found for ' || p_table_name);
END kill_locks_table;

The above stored procedure can check the locked object according to the given user name and table name and automatically terminate the corresponding session to achieve unlocking.

②: Use the stored procedure to unlock:

call kill_locks_table('用户名', '对象名');

Among them, 'user name' refers to the owner name of the table, 'object name' refers to the name of the locked table, execute the above The Pl/SQL statement can unlock all locks on the specified table.

Summary

Deleting Oracle locks can be done manually, using PL/SQL, etc., and the most appropriate method can be selected according to different situations. Before operating, be sure to determine the target object and related session information to avoid misoperation.

The above is the detailed content of How to delete oracle lock (a brief analysis of the method). For more information, please follow other related articles on the PHP Chinese website!