반응형

/********************************************************************************************
-- 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;

반응형

+ Recent posts