SELECT s.sid,s.serial#,s.username, L.* FROM (
SELECT SID,TYPE ||
DECODE(TYPE,
'BL',': Buffer hash table',
'CF',': Control File Transaction',
'CI',': Cross Instance Call',
'CS',': Control File Schema',
'CU',': Bind Enqueue',
'DF',': Data File',
'DL',': Direct-loader index-creation',
'DM',': Mount/startup db primary/secondary instance',
'DR',': Distributed Recovery Process',
'DX',': Distributed Transaction Entry',
'FI',': SGA Open-File Information',
'FS',': File Set',
'IN',': Instance Number',
'IR',': Instance Recovery Serialization',
'IS',': Instance State',
'IV',': Library Cache InValidation',
'JQ',': Job Queue',
'KK',': Redo Log "Kick"',
'LS',': Log Start/Log Switch',
'MB',': Master Buffer hash table',
'MM',': Mount Definition',
'MR',': Media Recovery',
'PF',': Password File',
'PI',': Parallel Slaves',
'PR',': Process Startup',
'PS',': Parallel Slaves Synchronization',
'RE',': USE_ROW_ENQUEUE Enforcement',
'RT',': Redo Thread',
'RW',': Row Wait',
'SC',': System Commit Number',
'SH',': System Commit Number HWM',
'SM',': SMON',
'SQ',': Sequence Number',
'SR',': Synchronized Replication',
'SS',': Sort Segment',
'ST',': Space Transaction',
'SV',': Sequence Number Value',
'TA',': Transaction Recovery',
'TD',': DDL enqueue',
'TE',': Extend-segment enqueue',
'TM',': DML enqueue',
'TS',': Temporary Segment',
'TT',': Temporary Table',
'TX',': Transaction',
'UL',': User-defined Lock',
'UN',': User Name',
'US',': Undo Segment Serialization',
'WL',': Being-written redo log instance',
'WS',': Write-atomic-log-switch global enqueue',
'XA',': Instance Attribute',
'XI',': Instance Registration',
decode(substr(type,1,1),
'L', ': Library Cache ('||substr(type,2,1)||')',
'N', ': Library Cache Pin ('||substr(type,2,1)||')',
'Q', ': Row Cache ('||substr(type,2,1)||')',
': ????')) type,
DECODE(LMODE,
0, '--Waiting--',
1, 'Null',
2, 'Sub-Share',
3, 'Sub-Exclusive',
4, 'Share',
5, 'Share/Sub-Excl',
6, 'Exclusive',
'<Unknown>') mode_held,
DECODE(REQUEST,
0, '',
1, 'Null',
2, 'Sub-Share',
3, 'Sub-Exclusive',
4, 'Share',
5, 'Share/Sub-Excl',
6, 'Exclusive',
'<Unknown>') mode_requested,
COUNT(*) cnt
FROM GV$LOCK
WHERE TYPE NOT IN ('MR','RT')
GROUP BY SID,TYPE ||
DECODE(TYPE,
'BL',': Buffer hash table',
'CF',': Control File Transaction',
'CI',': Cross Instance Call',
'CS',': Control File Schema',
'CU',': Bind Enqueue',
'DF',': Data File',
'DL',': Direct-loader index-creation',
'DM',': Mount/startup db primary/secondary instance',
'DR',': Distributed Recovery Process',
'DX',': Distributed Transaction Entry',
'FI',': SGA Open-File Information',
'FS',': File Set',
'IN',': Instance Number',
'IR',': Instance Recovery Serialization',
'IS',': Instance State',
'IV',': Library Cache InValidation',
'JQ',': Job Queue',
'KK',': Redo Log "Kick"',
'LS',': Log Start/Log Switch',
'MB',': Master Buffer hash table',
'MM',': Mount Definition',
'MR',': Media Recovery',
'PF',': Password File',
'PI',': Parallel Slaves',
'PR',': Process Startup',
'PS',': Parallel Slaves Synchronization',
'RE',': USE_ROW_ENQUEUE Enforcement',
'RT',': Redo Thread',
'RW',': Row Wait',
'SC',': System Commit Number',
'SH',': System Commit Number HWM',
'SM',': SMON',
'SQ',': Sequence Number',
'SR',': Synchronized Replication',
'SS',': Sort Segment',
'ST',': Space Transaction',
'SV',': Sequence Number Value',
'TA',': Transaction Recovery',
'TD',': DDL enqueue',
'TE',': Extend-segment enqueue',
'TM',': DML enqueue',
'TS',': Temporary Segment',
'TT',': Temporary Table',
'TX',': Transaction',
'UL',': User-defined Lock',
'UN',': User Name',
'US',': Undo Segment Serialization',
'WL',': Being-written redo log instance',
'WS',': Write-atomic-log-switch global enqueue',
'XA',': Instance Attribute',
'XI',': Instance Registration',
decode(substr(type,1,1),
'L', ': Library Cache ('||substr(type,2,1)||')',
'N', ': Library Cache Pin ('||substr(type,2,1)||')',
'Q', ': Row Cache ('||substr(type,2,1)||')',
': ????')),
DECODE(LMODE,
0, '--Waiting--',
1, 'Null',
2, 'Sub-Share',
3, 'Sub-Exclusive',
4, 'Share',
5, 'Share/Sub-Excl',
6, 'Exclusive',
'<Unknown>'),
DECODE(REQUEST,
0, '',
1, 'Null',
2, 'Sub-Share',
3, 'Sub-Exclusive',
4, 'Share',
5, 'Share/Sub-Excl',
6, 'Exclusive',
'<Unknown>')
) L, V$SESSION S
WHERE S.SID=L.SID
/
Nenhum comentário:
Postar um comentário