.::: 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;
 

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#';