Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- v$bh has no PRIMARY KEY.
- Duplicate ROWS are possible.
- SQL> DESC v$bh
- Name NULL? TYPE
- ----------------------------------------------------------------------------------------------------------------- -------- ----------------------------------------------------------------------------
- FILE# NUMBER
- BLOCK# NUMBER
- CLASS# NUMBER
- STATUS VARCHAR2(10)
- XNC NUMBER
- FORCED_READS NUMBER
- FORCED_WRITES NUMBER
- LOCK_ELEMENT_ADDR RAW(8)
- LOCK_ELEMENT_NAME NUMBER
- LOCK_ELEMENT_CLASS NUMBER
- DIRTY VARCHAR2(1)
- TEMP VARCHAR2(1)
- PING VARCHAR2(1)
- STALE VARCHAR2(1)
- DIRECT VARCHAR2(1)
- NEW CHAR(1)
- OBJD NUMBER
- TS# NUMBER
- LOBID NUMBER
- CACHEHINT NUMBER
- FLASH_CACHE VARCHAR2(7)
- CELL_FLASH_CACHE VARCHAR2(7)
- CON_ID NUMBER
- SQL> SELECT COUNT(*)
- 2 FROM v$bh WHERE FILE#=3 AND BLOCK#=4954 AND status = 'cr'
- 3 ;
- COUNT(*)
- ----------
- 7
- SQL> SELECT COUNT(*)
- 2 FROM
- 3 (
- 4 SELECT DISTINCT
- 5 FILE#,
- 6 BLOCK#,
- 7 CLASS#,
- 8 STATUS,
- 9 XNC,
- 10 FORCED_READS,
- 11 FORCED_WRITES,
- 12 LOCK_ELEMENT_ADDR,
- 13 LOCK_ELEMENT_NAME,
- 14 LOCK_ELEMENT_CLASS,
- 15 DIRTY,
- 16 TEMP,
- 17 PING,
- 18 STALE,
- 19 DIRECT,
- 20 NEW,
- 21 OBJD,
- 22 TS#,
- 23 LOBID,
- 24 CACHEHINT,
- 25 FLASH_CACHE,
- 26 CELL_FLASH_CACHE,
- 27 CON_ID
- 28 FROM v$bh WHERE FILE#=3 AND BLOCK#=4954 AND status = 'cr'
- 29 );
- COUNT(*)
- ----------
- 1
- Here, explicitly, are 7 duplicate v$bh ROWS.
- SELECT * FROM v$bh
- WHERE FILE#=3 AND BLOCK#=4954 AND status = 'cr';
- FILE# BLOCK# CLASS# STATUS XNC FORCED_READS FORCED_WRITES LOCK_ELEMENT_ADDR LOCK_ELEMENT_NAME LOCK_ELEMENT_CLASS
- 3 4954 8 cr 0 0 0 00
- 3 4954 8 cr 0 0 0 00
- 3 4954 8 cr 0 0 0 00
- 3 4954 8 cr 0 0 0 00
- 3 4954 8 cr 0 0 0 00
- 3 4954 8 cr 0 0 0 00
- 3 4954 8 cr 0 0 0 00
- DIRTY TEMP PING STALE DIRECT NEW OBJD TS# LOBID CACHEHINT FLASH_CACHE CELL_FLASH_CACHE CON_ID
- N N N N N N 8107 1 0 15 DEFAULT DEFAULT 1
- N N N N N N 8107 1 0 15 DEFAULT DEFAULT 1
- N N N N N N 8107 1 0 15 DEFAULT DEFAULT 1
- N N N N N N 8107 1 0 15 DEFAULT DEFAULT 1
- N N N N N N 8107 1 0 15 DEFAULT DEFAULT 1
- N N N N N N 8107 1 0 15 DEFAULT DEFAULT 1
- N N N N N N 8107 1 0 15 DEFAULT DEFAULT 1
- No duplicate ROWS exist IN x$bh
- SQL> SELECT COUNT(*)
- 2 FROM x$bh x
- 3 WHERE x.obj = 8107
- 4 AND x.dbablk = 4954
- 5 AND x.state = 3;
- COUNT(*)
- ----------
- 7
- Ix x$bh, the ROWS are different BY ADDR, INDX, OR SCN
- SQL> SELECT x.addr, x.indx, x.con_id, x.dbarfil, x.dbablk, x.obj, cr_scn_bas, cr_scn_wrp, x.state
- 2 FROM x$bh x
- 3 WHERE x.obj = 8107
- 4 AND x.dbablk = 4954
- 5 AND x.state = 3
- 6 ORDER BY x.con_id, x.dbarfil, x.dbablk, x.obj, cr_scn_bas, cr_scn_wrp;
- ADDR INDX CON_ID DBARFIL DBABLK OBJ CR_SCN_BAS CR_SCN_WRP STATE
- ---------------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
- 00007F7BF89DDCA8 2518 1 3 4954 8107 31303060 0 3
- 00007F7BF89DDE00 2517 1 3 4954 8107 31304429 0 3
- 00007F7BF89DE420 2516 1 3 4954 8107 31304438 0 3
- 00007F7BF89DE578 2515 1 3 4954 8107 31304853 0 3
- 00007F7BF89DE6D0 2514 1 3 4954 8107 31304862 0 3
- 00007F7BF89DE828 2513 1 3 4954 8107 31305577 0 3
- 00007F7BF89DEAD8 2511 1 3 4954 8107 31305585 0 3
- 7 ROWS selected.
Advertisement
Add Comment
Please, Sign In to add comment