/********************************************************************************************
-- Title : [10g] LOCK 정보 출력 뷰
-- Key word :
********************************************************************************************/
create or replace view sys.sp_lock0
as
select s.sid, s.serial#, s.username, s.machine, s.program, s.osuser, o.object_id
, decode(o.owner, null, '', o.owner || '.' || o.object_name ) "OBJECT_NAME"
, l.type || '(' || decode(l.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'
, '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', l.type
) || ')' "LOCK_TYPE"
, l.block "BLK", s.blocking_session "BLKBY", s.status
, 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"
, s.event
, nvl((select sql_text from v$sql
where hash_value = s.sql_hash_value and address = s.sql_address
and parsing_schema_name = s.username
)
,(select sql_text from v$sql
where address = s.prev_sql_addr and parsing_schema_name = s.username
)
) "SQL_TEXT"
from v$lock l
inner join v$session s
on l.sid = s.sid
and s.type != 'BACKGROUND'
left join dba_objects o
on l.id1 = o.object_id
order by l.sid, l.type;
-- select * from sys.sp_lock0;
grant select on sp_lock0 to public;