Object which are all accessed and locked

Continue of previous Blog on performance << http://oracletechdba.blogspot.com/2014/06/sequence-to-analysis-database-level.html >>
 
Object which are all accessed and locked:
----------------------------------------
 
This will help when some object where locked or taking time for accessing some objects.

++ Find the all session ID of the object
 
select sid from v$access where object='&Object_name'
 
++Get the session details from the above

select OBJECT_ID,ORACLE_USERNAME,OS_USER_NAME,PROCESS,LOCKED_MODE from v$locked_object where SESSION_ID='&SessionID';




OR

select OBJECT_ID,ORACLE_USERNAME,OS_USER_NAME,PROCESS,LOCKED_MODE from v$locked_object where SESSION_ID in (select sid from v$access where object='&Object_name');


++ LOCK on object level

select distinct o.name ,       
decode(lh.lmode, 1, 'null', 2, 'row share', 3, 'row exclusive', 4,  'share',5, 'share row exclusive' , 6, 'exclusive')  "Lock Type"
,lw.ctime "Wait time",lh.ctime "Hold time"   from sys.obj$ o,v$lock lw,v$lock lh  where lh.id1  = o.obj#   and  lh.id1  = lw.id1
and lh.type = 'TM' and lw.type = 'TM' and o.name='&Object_name';

col owner format a12
col object_name format a25
col ORACLE_USERNAME format a15
col OS_USER_NAME format a15
col OBJECT_TYPE format a15
set lines 140
select l.OBJECT_ID,o.owner,o.OBJECT_NAME,o.OBJECT_TYPE,SESSION_ID,ORACLE_USERNAME,OS_USER_NAME,LOCKED_MODE from v$locked_object l,dba_objects o where l.OBJECT_ID=o.OBJECT_ID
/