select bh.inst_id,
bh.file#,
bh.block#,
bh.class#,
bh.status,
bh.xnc,
bh.forced_reads,
bh.forced_writes,
ob.name,
ob.subname partition_name,
decode (ob.type#, 1, 'INDEX',
2, 'TABLE',
3, 'CLUSTER',
4, 'VIEW',
5, 'SYNONYM',
6, 'SEQUENCE',
7, 'PROCEDURE',
8, 'FUNCTION',
9, 'PACKAGE',
10, 'NON-EXISTENT',
11, 'PACKAGE BODY',
12, 'TRIGGER',
13, 'TYPE',
14, 'TYPE BODY',
19, 'TABLE PARTITION',
20, 'INDEX PARTITION',
21, 'LOB',
22, 'LIBRARY',
28, 'JAVA SOURCE',
29, 'JAVA CLASS',
30, 'JAVA RESOURCE',
56, 'JAVA DATA',
'UNKNOWN') kind,
ob.owner#,
lock_element_addr "GC_ELEMENT_ADDR",
lock_element_name "GC_ELEMENT_NAME"
from gv$bh bh, "_CURRENT_EDITION_OBJ" ob
where (bh.objd = ob.dataobj#) and
(bh.forced_reads + bh.forced_writes) > 0
union all
select bh.inst_id,
bh.file#,
bh.block#,
bh.class#,
bh.status,
bh.xnc,
bh.forced_reads,
bh.forced_writes,
un.name,
NULL partition_name,
'UNDO' kind,
un.user# owner#,
lock_element_addr "GC_ELEMENT_ADDR",
lock_element_name "GC_ELEMENT_NAME"
from gv$bh bh, undo$ un
where (bh.class# >= 11) and
(un.us# = floor((bh.class# - 11) / 2)) and
(bh.forced_reads + bh.forced_writes) > 0
SELECT BH.INST_ID
,
BH.FILE#
,
BH.BLOCK#
,
BH.CLASS#
,
BH.STATUS
,
BH.XNC
,
BH.FORCED_READS
,
BH.FORCED_WRITES
,
OB.NAME
,
OB.SUBNAME PARTITION_NAME
,
DECODE (OB.TYPE#
, 1
, 'INDEX'
,
2
, 'TABLE'
,
3
, 'CLUSTER'
,
4
, 'VIEW'
,
5
, 'SYNONYM'
,
6
, 'SEQUENCE'
,
7
, 'PROCEDURE'
,
8
, 'FUNCTION'
,
9
, 'PACKAGE'
,
10
, 'NON-EXISTENT'
,
11
, 'PACKAGE BODY'
,
12
, 'TRIGGER'
,
13
, 'TYPE'
,
14
, 'TYPE BODY'
,
19
, 'TABLE PARTITION'
,
20
, 'INDEX PARTITION'
,
21
, 'LOB'
,
22
, 'LIBRARY'
,
28
, 'JAVA SOURCE'
,
29
, 'JAVA CLASS'
,
30
, 'JAVA RESOURCE'
,
56
, 'JAVA DATA'
,
'UNKNOWN') KIND
,
OB.OWNER#
,
LOCK_ELEMENT_ADDR "GC_ELEMENT_ADDR"
,
LOCK_ELEMENT_NAME "GC_ELEMENT_NAME"
FROM GV$BH BH
, "_CURRENT_EDITION_OBJ" OB
WHERE (BH.OBJD = OB.DATAOBJ#) AND
(BH.FORCED_READS + BH.FORCED_WRITES) > 0
UNION ALL
SELECT BH.INST_ID
,
BH.FILE#
,
BH.BLOCK#
,
BH.CLASS#
,
BH.STATUS
,
BH.XNC
,
BH.FORCED_READS
,
BH.FORCED_WRITES
,
UN.NAME
,
NULL PARTITION_NAME
,
'UNDO' KIND
,
UN.USER# OWNER#
,
LOCK_ELEMENT_ADDR "GC_ELEMENT_ADDR"
,
LOCK_ELEMENT_NAME "GC_ELEMENT_NAME"
FROM GV$BH BH
, UNDO$ UN
WHERE (BH.CLASS# >= 11) AND
(UN.US# = FLOOR((BH.CLASS# - 11) / 2)) AND
(BH.FORCED_READS + BH.FORCED_WRITES) > 0
|
|
|