.::: Script to kill Locking in Database Oracle using oracle & root User :::.

 

1. run from https://teguhth.blogspot.com/2024/11/how-to-sample-simulation-blocking.html

export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export ORACLE_SID=tgh
export PATH=$ORACLE_HOME/bin:$PATH

 

or 

 

export ORACLE_HOME=/u01/app/oracle/product/12.2.0/dbhome_1 
export ORACLE_SID=tgh 
export PATH=$ORACLE_HOME/bin:$PATH 

2. original script locking
SELECT
    s1.sid AS waiting_session,
    s1.serial# AS waiting_serial,
    s1.username AS waiting_user,
    s1.status AS waiting_status,
    s2.sid AS blocking_session,
    s2.serial# AS blocking_serial,
    s2.username AS blocking_user,
    s2.status AS blocking_status,
 s1.event,
    'ALTER SYSTEM KILL SESSION ''' || s1.sid || ',' || s1.serial# || ''' IMMEDIATE;' AS query_kill
FROM
    v$session s1
JOIN
    v$session s2
ON
    s1.blocking_session = s2.sid;
  

3. script running query 

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.osuser,
    s.machine,
    s.program,
    s.blocking_session,
    s.blocking_session_status,
    s.final_blocking_session,
    s.event,
    DBMS_LOB.SUBSTR(q.sql_fulltext, 4000, 1) AS full_sql_text,
    q.executions,
    q.cpu_time,
    q.elapsed_time
FROM
    v$session s
JOIN
    v$sql q ON s.sql_id = q.sql_id
WHERE
    s.username = 'AISYAH'
ORDER BY
    q.cpu_time DESC;
    
3. run query as using Oracle User 
[oracle@teguhth locking]$ pwd
/home/oracle/script/locking
[oracle@teguhth locking]$

[oracle@teguhth locking]$ sh kill_blocking_session_echo.sh
ALTER SYSTEM KILL SESSION '118,33657' IMMEDIATE
[oracle@teguhth locking]$
[oracle@teguhth locking]$
 

4. script kill blocking using oracle user

[oracle@teguhth locking]$ pwd
/home/oracle/script/locking
[oracle@teguhth locking]$
[oracle@teguhth locking]$ id
uid=54321(oracle) gid=54321(oinstall) groups=54321(oinstall),54322(dba),54323(oper),54324(backupdba),54325(dgdba),54326(kmdba),54330(racdba)
[oracle@teguhth locking]$
[oracle@teguhth locking]$ cat kill_blocking_session_echo.sh
#!/bin/bash

export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export ORACLE_SID=tgh
export PATH=$ORACLE_HOME/bin:$PATH

sqlplus -s / as sysdba <<'EOF'
SET SERVEROUTPUT ON
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET VERIFY OFF
SET ECHO OFF
SET TRIMSPOOL ON

DECLARE
  v_sql VARCHAR2(4000);
BEGIN
  FOR r IN (
    SELECT s1.sid, s1.serial#
    FROM v$session s1
    JOIN v$session s2
      ON s1.blocking_session = s2.sid
    WHERE s1.sid <> SYS_CONTEXT('USERENV','SID')
      AND s1.status <> 'KILLED'
      AND s1.username NOT IN ('SYS','SYSTEM')
  ) LOOP

    v_sql :=
      'ALTER SYSTEM KILL SESSION ''' ||
      r.sid || ',' || r.serial# ||
      ''' IMMEDIATE';

    -- ECHO command
    DBMS_OUTPUT.PUT_LINE(v_sql);

    -- EXECUTE
    EXECUTE IMMEDIATE v_sql;

  END LOOP;
END;
/
EXIT
EOF

[oracle@teguhth locking]$

5. run script kill blocking using root user

[root@teguhth kill]# pwd
/data/kill
[root@teguhth kill]# id
uid=0(root) gid=0(root) groups=0(root)
[root@teguhth kill]#
[root@teguhth kill]# sh kill_blocking_session_as_echo_root.sh
Last login: Mon Jan 12 14:34:50 WIB 2026
ALTER SYSTEM KILL SESSION '108,4927' IMMEDIATE
[root@teguhth kill]#
 

6. run script kill blocking using root user

[root@teguhth kill]# pwd
/data/kill
[root@teguhth kill]# id
uid=0(root) gid=0(root) groups=0(root)
[root@teguhth kill]#
[root@teguhth kill]# cat kill_blocking_session_as_echo_root.sh
#!/bin/bash

su - oracle <<'EOF'
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export ORACLE_SID=tgh
export PATH=$ORACLE_HOME/bin:$PATH

sqlplus -s / as sysdba <<'SQL'
SET SERVEROUTPUT ON
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET VERIFY OFF
SET ECHO OFF
SET TRIMSPOOL ON

DECLARE
  v_sql VARCHAR2(4000);
BEGIN
  FOR r IN (
    SELECT s1.sid, s1.serial#
    FROM v$session s1
    JOIN v$session s2
      ON s1.blocking_session = s2.sid
    WHERE s1.sid <> SYS_CONTEXT('USERENV','SID')
      AND s1.status <> 'KILLED'
      AND s1.username NOT IN ('SYS','SYSTEM')
  ) LOOP
    v_sql :=
      'ALTER SYSTEM KILL SESSION ''' ||
      r.sid || ',' || r.serial# ||
      ''' IMMEDIATE';

    DBMS_OUTPUT.PUT_LINE(v_sql);
    EXECUTE IMMEDIATE v_sql;
  END LOOP;
END;
/
EXIT
SQL
EOF

[root@teguhth kill]#