Monish's DBA Blog

Session: 1

SQL> select * from dept;
DEPTNO     DNAME          LOC
---------- -------------- -------------
10         ACCOUNTING     NEW YORK
20         RESEARCH       DALLAS
30         SALES          CHICAGO
40         OPERATIONS     BOSTON


Lock one row.

SQL> update dept set dname='DEV' where deptno=30;
1 row updated.

Do not commit

Session: 2

Try to lock the same row

SQL> update dept set dname='TESTING' where deptno=30;

Execute this srcipts to gather lock statistic.

SQL> select (select username from v$session where sid=a.sid) BLOCKER,
2 a.sid,
3 'IS BLOCKING',
4 (select username from v$session where sid=b.sid) BLOCKEE,
5 b.sid
6 from v$lock a, v$lock b
7 where a.block = 1
8 and b.request > 0
9 and a.id1 = b.id1
10 and b.id2 = b.id2
11 /


BLOCKER                        SID        'ISBLOCKING BLOCKEE                        SID
------------------------------ ---------- ----------- ------------------------------ ----------
SCOTT                          146        IS BLOCKING SCOTT                          151



SQL> col OBJ format a15
SQL> col SS format a15
SQL> set linesize 200


SQL> select owner||'.'||object_name obj
2 ,oracle_username||' ('||s.status||')' oruser
3 ,os_user_name osuser
4 ,l.process unix
5 ,''''||s.sid||','||s.serial#||'''' ss
6 ,r.name rs
7 ,to_char(s.logon_time,'yyyy/mm/dd hh24:mi:ss') time
8 from v$locked_object l
9 ,dba_objects o
10 ,v$session s
11 ,v$transaction t
12 ,v$rollname r
13 where l.object_id = o.object_id
14 and s.sid=l.session_id
15 and s.taddr=t.addr
16 and t.xidusn=r.usn
17 order by osuser, ss, obj
18 /


OBJ             ORUSER            OSUSER                         UNIX         SS RS TIME
--------------- ----------------- ------------------------------ ------------ --------------- ------------------------------ -------------------
SCOTT.DEPT      SCOTT (INACTIVE)  oracle                          17809       '146,188'

_SYSSMU1$ 2009/08/04 06:03:37

here ss means ,

Session: 3

Kill the session to release the lock:

ALTER SYSTEM KILL SESSION ‘,’;


SQL> alter system kill session '146,188';

System altered.

========================

Readers are leaders. You can only master the craft if you constantly study and keep up the hands-on practice. Following are some of the great books for your reference.

Please use the below links to grab your copy. Thank you! 🙂

Expert Oracle Database Architecture (by Thomas Kyte and Darl Kuhn) –> https://amzn.to/2EZ8eUE

Expert Oracle Database Architecture: Oracle Database 9i, 10g, and 11g Programming Techniques and Solutions –> https://amzn.to/2F17qPh

Oracle Database 12c DBA Handbook (Oracle Press) –> https://amzn.to/2F0tJoh
RMAN Recipes for Oracle Database 12c-A Problem-Solution Approach –> https://amzn.to/2EYY3ja
Oracle Database 12c Performance Tuning Recipes –> https://amzn.to/2F01uGj

Oracle RMAN 11g Backup and Recovery (Oracle Press) –> https://amzn.to/2NQ3OT9

OCA/OCP Oracle Database 12c All-in-One Exam Guide (Exams 1Z0-061, 1Z0-062, & 1Z0-063) –> https://amzn.to/2F0jTD0

OCA/OCP Oracle Database 11g All-in-One Exam Guide with CD-ROM –> https://amzn.to/2SPPJWJ

Expert Oracle RAC Performance Diagnostics and Tuning –> https://amzn.to/2NTXxWr

Pro Oracle Database 11g Administration (Expert’s Voice in Oracle) –> https://amzn.to/2NRj0iD