.::: How to Sample Simulation Blocking Locking Oracle Database & Trouble shoot & check running query process :::.

 
1. create table sample

CREATE TABLE locking_test (
    id NUMBER PRIMARY KEY,
    data VARCHAR2(50)
);

INSERT INTO locking_test (id, data) VALUES (1, 'Initial Data');
commit;

2. open 2 or 3 terminal

Buka Dua Sesi di SQL*Plus atau Alat Lainnya
Anda perlu dua sesi terpisah (misalnya, di SQL*Plus atau alat database lain seperti SQL Developer) untuk mensimulasikan locking. Sesi 1 akan melakukan UPDATE tanpa melakukan COMMIT atau ROLLBACK, sehingga menahan lock di baris tersebut. Sesi 2 akan mencoba melakukan operasi yang sama dan akan mengalami blocking sampai lock dilepas.

3. run command

-- Sesi 1: Mulai Transaksi in terminal 1
UPDATE locking_test SET data = 'Updated by Session 1' WHERE id = 1;

-- Jangan lakukan COMMIT atau ROLLBACK

-- Sesi 2: Coba lakukan UPDATE pada baris yang sama in terminal 2

UPDATE locking_test SET data = 'Updated by Session 2' WHERE id = 1;

4. check locking n processlist

SELECT s.inst_id,
       s.sid,
       s.blocking_session AS blk_by,
       s.serial#,
       s.username,
       s.status,
       s.osuser,
       s.machine,
       s.program,
       s.module,
       s.event,
       s.sql_id,
       q.sql_text,
       ROUND(s.last_call_et / 60, 2) AS elapsed_minutes,       
       s.wait_class,
       s.wait_time,
       s.state,
       s.logon_time
FROM gv$session s
JOIN gv$sql q
ON s.sql_id = q.sql_id
--WHERE s.status = 'ACTIVE'
  --AND s.sid != SYS_CONTEXT('USERENV', 'SID')
ORDER BY elapsed_minutes DESC;

SELECT s.sid, , s.blocking_session AS blk_by,s.serial#, s.username, s.osuser, s.machine, s.program, q.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 = 'tgh' -- Your user or adjust if needed
ORDER BY q.cpu_time DESC;
 

 

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;
 

5. end session

-- Sesi 1: Lepaskan lock dengan menyelesaikan transaksi open terminal 1
COMMIT;
or
rollback

6. if want to end

ALTER SYSTEM KILL SESSION 'sid,serial#';

 

B. Query Checking
1. checking proceslit locking

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;
 

2. check locking with query kill 

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. check locking with kill from oracle    

SELECT
    s.sid,
    s.serial#,
    s.username,
    s.status,
    s.osuser,
    s.machine,
    s.program, 

s.event,
    'ALTER SYSTEM KILL SESSION ''' || s.sid || ',' || s.serial# || ''' IMMEDIATE;' AS query_kill
FROM
    v$session s
WHERE
    s.sid = 72;
 

4. if kill from os
    
SELECT p.spid,'kill -9 ' ||p.spid||'' AS query_kill
FROM v$session s
JOIN v$process p ON s.paddr = p.addr
WHERE s.sid = 72;