Already a member?
Sign in
Welcome! This is a website that everyone can build together. It's easy!
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
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)
Edited by olapdba
206 words added
view changes
- complete history)
Keyword tags:
None
More Info: links to this page
