Sign in or 
Share this
Determine OLAP option Multidimensional Datatypes in the Buffer Cache
set lines 110 pages 500
col typ format a10 heading TYPE
col obj format a65 heading OWNER.OBJECT(SUBOBJECT)
col blocks format 999,999,999
select owner||'.'||object_name||'('||subobject_name||')' obj,
decode(object_type, 'INDEX PARTITION', 'IDX PTN', 'INDEX SUBPARTITION', 'IDX SUBP',
'LOB PARTITION', 'LOB PTN', 'LOB SUBPARTITION', 'LOB SUBP',
'TABLE PARTITION', 'TBL PTN', 'TABLE SUBPARTITION', 'TBL SUBP',
object_type) typ, count(1) blocks
from dba_objects dbao, v$bh vbh
where dbao.object_id = vbh.objd and dbao.owner in
(select username from v$session where sid in
(select session_id from v$aw_calc))
group by owner, object_name, subobject_name, object_type
order by count(1) desc;
OWNER.OBJECT(SUBOBJECT).............................. TYPE............ BLOCKS
----------------------------------------------------- ---------- ------------
SCOTT.REPORT_I$().................................... INDEX.............. 300
SCOTT.AW$REPORT(SYS_SUBP1814)........................ TBL SUBP............ 77
SCOTT.SYS_LOB0000085040C00004$$(SYS_LOB_SUBP1773).... LOB SUBP............ 37
SCOTT.SAMPLE_I$().................................... INDEX................31
SCOTT.SYS_LOB0000085040C00004$$(SYS_LOB_SUBP1781).... LOB SUBP............ 29
SCOTT.SYS_LOB0000085040C00004$$(SYS_LOB_SUBP1778).... LOB SUBP............ 22
SCOTT.AW$REPORT(SYS_SUBP1807)........................ TBL SUBP............ 14
SCOTT.AW$REPORT(SYS_SUBP1806)........................ TBL SUBP............ 14
SCOTT.AW$REPORT(SYS_SUBP1801)........................ TBL SUBP............ 14
SCOTT.AW$REPORT(SYS_SUBP1805)........................ TBL SUBP............ 14
SCOTT.AW$REPORT(SYS_SUBP1803)........................ TBL SUBP............ 14
SCOTT.AW$REPORT(SYS_SUBP1802)........................ TBL SUBP............ 14
SCOTT.AW$REPORT(SYS_SUBP1808)........................ TBL SUBP............ 14
SCOTT.AW$REPORT(SYS_SUBP1804)........................ TBL SUBP............ 13
SCOTT.SYS_LOB0000085040C00004$$(SYS_LOB_SUBP1782).... LOB SUBP............ 12
SCOTT.SYS_LOB0000085040C00004$$(SYS_LOB_SUBP1775).... LOB SUBP..............9
SCOTT.AW$SAMPLE(SYS_SUBP1764)........................ TBL SUBP..............8
SCOTT.AW$SAMPLE(SYS_SUBP1757)........................ TBL SUBP..............8
SCOTT.SYS_IL0000085040C00004$$(SYS_IL_SUBP1790)...... IDX SUBP..............8
col typ format a10 heading TYPE
col obj format a65 heading OWNER.OBJECT(SUBOBJECT)
col blocks format 999,999,999
select owner||'.'||object_name||'('||subobject_name||')' obj,
decode(object_type, 'INDEX PARTITION', 'IDX PTN', 'INDEX SUBPARTITION', 'IDX SUBP',
'LOB PARTITION', 'LOB PTN', 'LOB SUBPARTITION', 'LOB SUBP',
'TABLE PARTITION', 'TBL PTN', 'TABLE SUBPARTITION', 'TBL SUBP',
object_type) typ, count(1) blocks
from dba_objects dbao, v$bh vbh
where dbao.object_id = vbh.objd and dbao.owner in
(select username from v$session where sid in
(select session_id from v$aw_calc))
group by owner, object_name, subobject_name, object_type
order by count(1) desc;
OWNER.OBJECT(SUBOBJECT).............................. TYPE............ BLOCKS
----------------------------------------------------- ---------- ------------
SCOTT.REPORT_I$().................................... INDEX.............. 300
SCOTT.AW$REPORT(SYS_SUBP1814)........................ TBL SUBP............ 77
SCOTT.SYS_LOB0000085040C00004$$(SYS_LOB_SUBP1773).... LOB SUBP............ 37
SCOTT.SAMPLE_I$().................................... INDEX................31
SCOTT.SYS_LOB0000085040C00004$$(SYS_LOB_SUBP1781).... LOB SUBP............ 29
SCOTT.SYS_LOB0000085040C00004$$(SYS_LOB_SUBP1778).... LOB SUBP............ 22
SCOTT.AW$REPORT(SYS_SUBP1807)........................ TBL SUBP............ 14
SCOTT.AW$REPORT(SYS_SUBP1806)........................ TBL SUBP............ 14
SCOTT.AW$REPORT(SYS_SUBP1801)........................ TBL SUBP............ 14
SCOTT.AW$REPORT(SYS_SUBP1805)........................ TBL SUBP............ 14
SCOTT.AW$REPORT(SYS_SUBP1803)........................ TBL SUBP............ 14
SCOTT.AW$REPORT(SYS_SUBP1802)........................ TBL SUBP............ 14
SCOTT.AW$REPORT(SYS_SUBP1808)........................ TBL SUBP............ 14
SCOTT.AW$REPORT(SYS_SUBP1804)........................ TBL SUBP............ 13
SCOTT.SYS_LOB0000085040C00004$$(SYS_LOB_SUBP1782).... LOB SUBP............ 12
SCOTT.SYS_LOB0000085040C00004$$(SYS_LOB_SUBP1775).... LOB SUBP..............9
SCOTT.AW$SAMPLE(SYS_SUBP1764)........................ TBL SUBP..............8
SCOTT.AW$SAMPLE(SYS_SUBP1757)........................ TBL SUBP..............8
SCOTT.SYS_IL0000085040C00004$$(SYS_IL_SUBP1790)...... IDX SUBP..............8
olapdba |
Latest page update: made by olapdba
, Apr 17 2008, 11:55 AM EDT
(about this update
About This Update
Edited by olapdba
206 words added view changes - complete history) |
|
Keyword tags:
None
More Info: links to this page
|
There are no threads for this page.
Be the first to start a new thread.
