Blocking Session, Locks, Long Running Jobs Queries

Blocking Session:
=================
select blocking_session, sid, serial#, wait_class,seconds_in_wait From v$session where blocking_session is not NULL order by blocking_session;

Locks:
======
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;

To Estimate Time to Complete ahead for Long Running Jobs:
=========================================================
select SID,SERIAL#,SOFAR,TOTALWORK,UNITS,to_char(START_TIME,'DD-MON-YY HH:MI:SS'),TiME_REMAINING from v$session_longops where sofar < totalwork;

Note: If above Query fails try removing spaces in both sides for < nd try

Table Locks
============
SELECT a.SID,SERIAL#,TERMINAL,USERNAME,B.OBJECT_ID,C.OBJECT_NAME ,PROGRAM,
d.TYPE lock_type,decode (lmode,3,'SX',6,'X',4,'S',5,'SSX') lock_mode
FROM V$SESSION A,V$LOCKED_OBJECT B, ALL_OBJECTS C,v$lock d
WHERE A.SID = B.SESSION_ID
and b.session_id = d.sid
and b.object_id = d.id1
AND B.OBJECT_ID = C.OBJECT_ID
and b.object_id=38350;

Identify the sid -- kill the session

Lock on a table
-------------------------
SQL> select distinct  sid from v$lock  v1  where 1=1  and v1.sid in (select v2.sid from dba_objects do, v$lock v2 where do.object_name = 'MLC_ASN_STORAGE'
and do.object_id= v2.id1 )     and v1.request = 0     order by v1.sid ;