Hi All,
Oracle v 11.2.0.2
I am trying to check how much space has various objects taken up in the buffer cache. After some search on internet, found out about the view V$BH.
Please check my query on v$bh and the output
SQL> l
1 select objd, object_name, object_type, block_count, (block_count * 8192)/(1024 * 1024) size_mb
2 from dba_objects a,
3 (select objd, count(*) block_count from v$bh group by objd having count(*) > 500) b -- objs with more than 500 blocks
4* where a.object_id (+)= b.objd
SQL> /
OBJD OBJECT_NAME OBJECT_TYPE BLOCK_COUNT SIZE_MB
---------- -------------------------------- ------------------- ----------- --------------------
2 C_OBJ# CLUSTER 950 7.422
260 SMON_SCN_TO_TIME_AUX CLUSTER 1573 12.289
407 C_OBJ#_INTCOL# CLUSTER 3381 26.414
410 I_H_OBJ#_COL# INDEX 1062 8.297
449 WRI$_OPTSTAT_TAB_HISTORY TABLE 2834 22.141
452 WRI$_OPTSTAT_IND_HISTORY TABLE 3519 27.492
455 WRI$_OPTSTAT_HISTHEAD_HISTORY TABLE 23616 184.500
458 I_WRI$_OPTSTAT_HH_OBJ_ICOL_ST INDEX 1255 9.805
460 WRI$_OPTSTAT_HISTGRM_HISTORY TABLE 9477 74.039
461 I_WRI$_OPTSTAT_H_OBJ#_ICOL#_ST INDEX 2821 22.039
462 I_WRI$_OPTSTAT_H_ST INDEX 1061 8.289
490 C_TOID_VERSION# CLUSTER 1481 11.570
5509 WRI$_ADV_PARAMETERS_PK INDEX 674 5.266
5983 WRH$_SQL_PLAN TABLE 2232 17.438
6135 WRH$_SYSMETRIC_HISTORY TABLE 2146 16.766
6136 WRH$_SYSMETRIC_HISTORY_INDEX INDEX 1035 8.086
6137 WRH$_SYSMETRIC_SUMMARY TABLE 769 6.008
53982 AUD$ TABLE 642 5.016
171618 PK_AAA_LOCKS INDEX 2466 19.266
171703 PERSON_INFO TABLE 1127 8.805
171720 TEMP_PERSON_INFO TABLE 32052 250.406
171796 I_PPP_LOCKS_1 INDEX 2557 19.977
171807 PK_WORKFLOW_ITEMS INDEX 4655 36.367
171843 TEMP_REQUESTS_PK INDEX 44294 346.047
695543 OBJ$ TABLE 745 5.820
695545 I_OBJ2 INDEX 612 4.781
696055 ITEM_PRICES_UK INDEX 149044 1,164.406
696054 ITEM_PRICES TABLE 165525 1,293.164
696057 ITEM_PRICES_EXTRA_UK INDEX 202604 1,582.844
696056 ITEM_PRICES_EXTRA TABLE 429236 3,353.406
696083 82655 645.742
696073 159389 1,245.227
696086 7227 56.461
4294967295 112669 880.227
685633 8512 66.500
685630 582 4.547
696076 18683 145.961
696080 33017 257.945
696079 38113 297.758
696074 39102 305.484
696075 45825 358.008
685631 2507 19.586
696078 49840 389.375
696081 108872 850.563
696082 100294 783.547
696084 10652 83.219
696077 27614 215.734
685632 2647 20.680
My question is, what are these object ids which has no corresponding entry in DBA_OBJECTS (i.e. blank object names in above output) ??
Can they be instances of global temporary tables? This is just a guess because in this application, global temporary tables are used a lot.
Thanks in advance