UNLOCK ORACLE OBJECT PL/SQL SCRIPT

UNLOCK_ORACLE_OBJECT_PLSQL_SCRIPT.sql

declare
  v_object_name    ALL_OBJECTS.OBJECT_NAME%type;
  v_object_id      ALL_OBJECTS.OBJECT_ID%type;
  v_session_id     V$LOCK.SID%type;
  v_session_serial V$LOCK.serial#%type;
  v_sql_stmnt      varchar2(400);
begin
  -- RUN AS SYSDBA
  -- PUT YOUR OBJECT NAME HERE!!!
  v_object_name := 'CER_CERTIFICACIONES_EVALUACION'; 
  -- GET OBJECT ID
  begin
    dbms_output.put_line('Getting object id by name' || v_object_name);
    select OBJECT_ID into v_object_id
    from ALL_OBJECTS
    WHERE OBJECT_NAME = v_object_name;
    exception when no_data_found
    then dbms_output.put_line('Object id NOT FOUND' || v_object_name);
    dbms_output.put_line('OBJECT_ID: ' || v_object_id);
  end;
  -- GET SESSION ID
  begin
    SELECT SID into v_session_id FROM V$LOCK WHERE id1 = v_object_id;
    exception when no_data_found
    then dbms_output.put_line('Object ' || v_object_name || ' is NOT locked');
    dbms_output.put_line('Object ' || v_object_name || ' is LOCCKED');
  end;
  -- GET SESSION SERIAL
  begin
    SELECT serial# into v_session_serial FROM v$session WHERE sid = v_session_id;
    dbms_output.put_line('Session id ' || v_session_id || ' , session serial' || v_session_serial);
  end;
  -- KILL SESSION
  begin
    IF NOT NVL(v_session_id) THEN
      v_sql_stmnt := 'ALTER SYSTEM KILL SESSION' || v_session_id || ',' || v_session_serial;
      execute immediate v_sql_stmnt;
    END IF;
  end;
end;