create view seg_locks as
   select nvl(S.USERNAME,'Internal') username,
          nvl(S.TERMINAL,'None') terminal,
          L.SID||','||S.SERIAL# Kill,
          U1.NAME||'.'||substr(T1.NAME,1,20) tab,
          decode(L.LMODE,1,'No Lock',
                 2,'Row Share',
                 3,'Row Exclusive',
                 4,'Share',
                 5,'Share Row Exclusive',
                 6,'Exclusive',null) lmode,
          decode(L.REQUEST,1,'No Lock',
                 2,'Row Share',
                 3,'Row Exclusive',
                 4,'Share',
                 5,'Share Row Exclusive',
                 6,'Exclusive',null) request
          from V$LOCK L,
               V$SESSION S,
               SYS.USER$ U1,
               SYS.OBJ$ T1
where L.SID = S.SID
  and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
  and U1.USER# = T1.OWNER#
  and S.TYPE != 'BACKGROUND';



drop view DBA_LOCKS;
drop view DBA_LOCK;
create view DBA_LOCK as
  select 
	sid session_id,
	decode(type, 
		'MR', 'Media Recovery', 
		'RT', 'Redo Thread',
		'UN', 'User Name',
		'TX', 'Transaction',
		'TM', 'DML',
		'UL', 'PL/SQL User Lock',
		'DX', 'Distributed Xaction',
		'CF', 'Control File',
		'IS', 'Instance State',
		'FS', 'File Set',
		'IR', 'Instance Recovery',
		'ST', 'Disk Space Transaction',
		'TS', 'Temp Segment',
		'IV', 'Library Cache Invalidation',
		'LS', 'Log Start or Switch',
		'RW', 'Row Wait',
		'SQ', 'Sequence Number',
		'TE', 'Extend Table',
		'TT', 'Temp Table',
		type) lock_type,
	decode(lmode, 
		0, 'None',           /* Mon Lock equivalent */
		1, 'Null',           /* N */
		2, 'Row-S (SS)',     /* L */
		3, 'Row-X (SX)',     /* R */
		4, 'Share',          /* S */
		5, 'S/Row-X (SSX)',  /* C */
		6, 'Exclusive',      /* X */
		to_char(lmode)) mode_held,
         decode(request,
		0, 'None',           /* Mon Lock equivalent */
		1, 'Null',           /* N */
		2, 'Row-S (SS)',     /* L */
		3, 'Row-X (SX)',     /* R */
		4, 'Share',          /* S */
		5, 'S/Row-X (SSX)',  /* C */
		6, 'Exclusive',      /* X */
		to_char(request)) mode_requested,
         to_char(id1) lock_id1, to_char(id2) lock_id2,
	 ctime last_convert,
	 decode(block,
	        0, 'Not Blocking',  /* Not blocking any other processes */
		1, 'Blocking',      /* This lock blocks other processes */
		2, 'Global',        /* This lock is global, so we can't tell */
		to_char(block)) blocking_others
      from v$lock;
drop public synonym DBA_LOCK;
create public synonym DBA_LOCK for DBA_LOCK;
grant select on DBA_LOCK to select_catalog_role;
drop public synonym DBA_LOCKS;
create public synonym DBA_LOCKS for DBA_LOCK;



create view seg_locks as
   select nvl(S.USERNAME,'Internal') username,
          nvl(S.TERMINAL,'None') terminal,
          L.SESSION_ID||','||S.SERIAL# Kill,
          U1.NAME||'.'||substr(T1.NAME,1,20) tab,
          L.MODE_HELD,L.MODE_REQUESTED
          from DBA_LOCK L,
               V$SESSION S,
               SYS.USER$ U1,
               SYS.OBJ$ T1
where L.SESSION_ID = S.SID
  and T1.OBJ# = decode(L.ID2,0,L.ID1,L.ID2)
  and U1.USER# = T1.OWNER#
  and S.TYPE != 'BACKGROUND';
