who are waiting for same record?
====================================
Solutions:
=========
1.
SELECT * FROM V$SESSION
WHERE trim(row_wait_obj#||row_wait_file#||row_wait_block#||row_wait_row#) =
(SELECT trim(row_wait_obj#||row_wait_file#||row_wait_block#||row_wait_row#)
FROM (
-------------------------------------------
select sid,d.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# ) rowidd
from v$session s, dba_objects d
where
s.ROW_WAIT_OBJ# = d.OBJECT_ID
and owner='STLBAS'
and OBJECT_TYPE='TABLE'
and sid=:SID
-------------------------------------------
))
select * from test ---object_name
where rowid='AAARJHAAGAACd13AAA'
2. Any rows returned are direct evidence of the problem
=======================================================
SELECT DECODE(request,0,'Holder: ','Waiter: ')||sid sess,
id1, id2, lmode, request, type
FROM V$LOCK
WHERE (id1, id2, type) IN
(SELECT id1, id2, type FROM V$LOCK WHERE request>0)
ORDER BY id1, request
3. can also give interesting information about session waiting for lock.
=======================================================================
$ORACLE_HOME/rdbms/admin/utllockt.sql
4. Script to find locks in the database
=======================================
select
(select username || ' - ' || osuser from v$session where sid=a.sid) blocker,
a.sid || ', ' ||
(select serial# from v$session where sid=a.sid) sid_serial,
' is blocking ',
(select username || ' - ' || osuser from v$session where sid=b.sid) blockee,
b.sid || ', ' ||
(select serial# from v$session where sid=b.sid) sid_serial
from v$lock a, v$lock b
where a.block = 1
and b.request > 0
and a.id1 = b.id1
and a.id2 = b.id2;
Halim is a Sr. Database Engineer/Data Architect (in Atlanta, USA) who is an Oracle certified (OCP) DBA, (OCP) Developer, Certified Cloud Architect Professional as well as OCI Autonomous DB specialist with extensive expertise in Database design, configuration, tuning, capacity planning, RAC, DG, Scripting, Python, PL/SQL etc. He achieved 16th position in worldwide first-ever PL/SQL Challenge cup playoff- http://plsql-challenge.blogspot.com/2010/07/winners-of-first-plsql-challenge.html
Thursday, January 28, 2010
Monday, January 25, 2010
efficiant way to check the table is empty
----very efficiant way to check the table is empty
--or
-----checking the first rows value
=====================================================
---empno is primary_key column
--or
---empno is index column
select /*+ FIRST_ROWS */ empno from emp where rownum = 1;
--or
-----checking the first rows value
=====================================================
---empno is primary_key column
--or
---empno is index column
select /*+ FIRST_ROWS */ empno from emp where rownum = 1;
Subscribe to:
Posts (Atom)
My Blog List
-
-
Index Usage – 21 week ago
-
-
-
-
-
-
-
-
Oracle Cloud & Third party tools3 years ago
-
-
-
Moving Sideways7 years ago
-
Upcoming Events...10 years ago
-