Check Database Locks

--To check database locks----[Kill Inactive Session] ----------------------------------------------------
SELECT * FROM dba_locks WHERE blocking_others='Blocking';

select LOCK_TYPE,SESSION_ID,BLOCKING_OTHERS from dba_locks where BLOCKING_OTHERS !='Not Blocking';

select process,sid, blocking_session from v$session where blocking_session is not null;

--Locked tables
SELECT l.session_id sid, o.object_name, o.object_type
FROM v$locked_object l, all_objects o
WHERE l.object_id = o.object_id

select SID,SERIAL#,MODULE,ACTION,CLIENT_IDENTIFIER,BLOCKING_SESSION_STATUS,STATE from v$session
where status='INACTIVE'
--where CLIENT_IDENTIFIER='%MONIR%'
AND MODULE like '%BOM%'
AND STATE='WAITING'
AND CLIENT_IDENTIFIER !='SYSADMIN'


--get SERIAL# where SID is from the above script
select * from v$session where SID=6563


--To kill the session with seesion id------------------------------------------------------------------
ALTER SYSTEM KILL SESSION '1351,993';

select * from v$session where process='144'



SELECT B.Owner, B.Object_Name, A.Oracle_Username, A.OS_User_Name 
FROM V$Locked_Object A, All_Objects B
WHERE A.Object_ID = B.Object_ID


select a.session_id,a.oracle_username, a.os_user_name, b.owner "OBJECT OWNER", b.object_name,b.object_type,a.locked_mode from
(select object_id, SESSION_ID, ORACLE_USERNAME, OS_USER_NAME, LOCKED_MODE from v$locked_object) a,
(select object_id, owner, object_name,object_type from dba_objects) b
where a.object_id=b.object_id;

SELECT a.object_id, a.session_id, substr(b.object_name, 1, 40)
FROM v$locked_object a, dba_objects b
WHERE a.object_id = b.object_id
AND b.object_name like 'BOM%'
ORDER BY b.object_name;




SELECT l.*, o.owner object_owner, o.object_name
FROM SYS.all_objects o, v$lock l
WHERE l.TYPE = 'TM'
AND o.object_id = l.id1
AND o.object_name in ('AP_INVOICES_ALL', 'AP_INVOICE_LINES_ALL', 'AP_INVOICE_DISTRIBUTIONS_ALL');




SELECT SID, SERIAL#
FROM v$session
WHERE SID = 960;



--R12: APXPAWKB Cannot Select this Payment Document Because it is in use By Another Single Payment (Doc ID 1322570.1)
--In the instance where user can reproduce the issue run the following queries:

1)

select * from dba_objects where object_name like 'CE_PAYMENT_DOCUMENTS'
and owner = 'CE';

2)

select * from v$locked_object where object_id in (select object_id from
dba_objects where object_name like 'CE_PAYMENT_DOCUMENTS'and owner = 'CE');

3)

select * from v$session where sid in (select session_id from
v$locked_object where object_id in (select object_id from dba_objects where
object_name like 'CE_PAYMENT_DOCUMENTS'and owner = 'CE'));

4)

select * from dba_locks where session_id in (select session_id from
v$locked_object where object_id in (select object_id from dba_objects where
object_name like ‘CE_PAYMENT_DOCUMENTS’and owner = ‘CE’));

Query result 3 provides the session which is having the lock of payment document.

Kill the session which is locking the 'CE_PAYMENT_DOCUMENTS' table

alter system kill session 'session,serial';