how to find out, who lock the same row?

session-1
=====================

create table LOCK_TEST (COL1 varchar2(1), COL2 varchar2(1));

Table created.

insert into LOCK_TEST values (12,'DATA1');

1 row created.

insert into LOCK_TEST values (54, 'DATA2');

1 row created.

select * from LOCK_TEST ;

commit ;


select * from LOCK_TEST for update ;



Session-2
================

update tstlock
set COL1='DATA1'
where COL1='DATA1';





Session-3(DBA)
================
select * from v$lock ;


select l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1
and l1.id2=l2.id2



select /*+ no_query_transformation first_rows */ l1.sid, ' IS BLOCKING ', l2.sid
from v$lock l1, v$lock l2
where l1.block =1 and l2.request > 0
and l1.id1=l2.id1 ;


==========================================JUST ENOUGH=============================================

select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

---SYS@WORKGROUP\USER-15FC74B60E ( SID=150 ) is blocking SYS@WORKGROUP\USER-15FC74B60E ( SID=148 )


select do.object_name,
row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do
where sid=:SID--148
and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;



select * from tstlock
where rowid='AAANpsAABAAAPNCAAA' ;

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


============================Information==============================

Lock type and the ID1 / ID2 columns
====================================
In this case, we already know that the blocking lock is an exclusive DML lock,
since we are the ones who issued the locking statement. But most of the time,
you wont be so lucky. Fortunately, you can read this information from the v$lock
table with little effort.

The first place to look is the TYPE column. There are dozens of lock types,
but the vast majority are system types. System locks are normally only held
for a very brief amount of time, and its not generally helpful to try to tune
your library cache, undo logs, etc. by looking in v$lock!


There are only three types of user locks,

1. TX,
2. TM
3. and UL.

1. UL is a user-defined lock a lock defined with the DBMS_LOCK package.

2. The TX lock is a row transaction lock; its acquired once for every transaction
that changes data, no matter how many objects you change in that transaction.
The ID1 and ID2 columns point to the rollback segment and transaction table
entries for that transaction.

3. The TM lock is a DML lock. It's acquired once for each object that's being changed.
The ID1 column identifies the object being modified.


Lock Modes
=====================
You can see more information on TM and TX locks just by looking at the lock modes.
The LMODE and REQUEST columns both use the same numbering for lock modes,
in order of increasing exclusivity: from 0 for no lock, to 6 for exclusive lock.
A session must obtain an exclusive TX lock in order to change data; LMODE will be 6.
If it cant obtain an exclusive lock because some of the rows it wants to change
are locked by another session, then it will request a TX in exclusive mode;
LMODE will be 0 since it does not have the lock, and REQUEST will be 6.
You can see this interaction in the rows we selected earlier from v$lock:


Note that ID1 and ID2 in Session 2, which is requesting the TX lock (LMODE=0, REQUEST=6),
point back to the rollback and transaction entries for Session 1.
Thats what lets us determine the blocking session for Session 2.


You may also see TX locks in mode 4, Shared mode. If a block containing rows
to be changed doesnt have any interested transaction list (ITL) entries left,
then the session acquires a TX lock in mode 4 while waiting for an ITL entry.
If you see contention for TX-4 locks on an object, you probably need to
increase INITRANS for the object.

TM locks are generally requested and acquired in modes 3,
Taka Shared-Row Exclusive, and 6. DDL requires a TM Exclusive lock.
(Note that CREATE TABLE doesnt require a TM lock -- it doesn't need to
lock any objects, because the object in question doesnt exist yet!) DML
requires a Shared-Row Exclusive lock. So, in the rows we selected earlier
from v$lock, you can see from the TM locking levels that these are DML locks:


Identifying the locked object
===================================

Now that we know that each TM row points to a locked object,
we can use ID1 to identify the object.


SQL> select object_name from dba_objects where object_id=55514 ;


Sometimes just knowing the object is enough information; but we can dig even deeper.
We can identify not just the object, but the block and even the
row in the block that Session 2 is waiting on.


Identifying the locked row
=============================

We can get this information from v$session by looking at the v$session
entry for the blocked session:


SQL> select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
from v$session where sid=148 ;

This gives us the object ID, the relative file number, the block in the datafile,
and the row in the block that the session is waiting on. If that list of
data sounds familiar, its because those are the four components of an extended ROWID.
We can build the rows actual extended ROWID from these components using
the DBMS_ROWID package. The ROWID_CREATE function takes these arguments
and returns the ROWID:


SQL> select do.object_name,
row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do
where sid=:148
and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;


And, of course, this lets us inspect the row directly.


SQL> select * from tstlock where rowid='AAAVnHAAQAAAp0tAAA' ;



select * from v$lock
where type='TM'


select object_name from dba_objects where object_id=55916 ; --(v$lock.id1 OF TM LOCK )


select row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#
from v$session where sid=148 --(SID OF TM LOCK )

---------------------------------------------------------------------------
===========================================================================


select s1.username || '@' || s1.machine
|| ' ( SID=' || s1.sid || ' ) is blocking '
|| s2.username || '@' || s2.machine || ' ( SID=' || s2.sid || ' ) ' AS blocking_status
from v$lock l1, v$session s1, v$lock l2, v$session s2
where s1.sid=l1.sid and s2.sid=l2.sid
and l1.BLOCK=1 and l2.request > 0
and l1.id1 = l2.id1
and l2.id2 = l2.id2 ;

---SYS@WORKGROUP\USER-15FC74B60E ( SID=150 ) is blocking SYS@WORKGROUP\USER-15FC74B60E ( SID=148 )


select do.object_name,
row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row#,
dbms_rowid.rowid_create ( 1, ROW_WAIT_OBJ#, ROW_WAIT_FILE#, ROW_WAIT_BLOCK#, ROW_WAIT_ROW# )
from v$session s, dba_objects do
where sid=:SID--148
and s.ROW_WAIT_OBJ# = do.OBJECT_ID ;



select * from tstlock
where rowid='AAANpsAABAAAPNCAAA' ;

move table

SELECT 'ALTER TABLE '|| OWNER ||'.'|| TABLE_NAME || CHR(10) ||
'MOVE TABLESPACE '||TABLESPACE_NAME ||';'
FROM DBA_TABLES
WHERE OWNER IN('TEST')
AND TEMPORARY ='N'

Move Index and rebuild index

---first create a folder C:/Tuning

set echo off
set feedback off
set pagesize 0
set verify off
PROMPT Enter the name of the application owner:
ACCEPT app_owner
PROMPT Enter the name of the new tablespace for the application indexes:
ACCEPT new_idx_tablespace

spool C:\TUNING\STIMG_MoveIndexes.tmp

-- rebuild all indexes on the moved tables, even those not owned
-- by the specified user because moving the tables will set their
-- status to UNUSABLE (unless they are IOT tables)

SELECT 'ALTER INDEX '||I.owner||'.'||I.index_name||CHR(10)||
'REBUILD TABLESPACE '||I.tablespace_name||' ONLINE PARALLEL;'
FROM DBA_INDEXES I,DBA_TABLES T
WHERE I.table_name = T.table_name
AND I.owner = T.owner
AND T.owner = UPPER('&app_owner');

-- rebuild any other indexes owned by this user that may not be on
-- the above tables

SELECT 'ALTER INDEX '||owner||'.'||index_name||CHR(10)||
'REBUILD TABLESPACE &new_idx_tablespace ONLINE PARALLEL;'
FROM dba_indexes
WHERE owner = UPPER('&&app_owner');

spool off

set echo on
set feedback on
set pagesize 60

spool C:\TUNING\STIMG_MoveIndexes.log

@C:\TUNING\STIMG_MoveIndexes.tmp

spool off