Determine Size and Owner of AWs in your Database

[ola...@gmail.com]

This is the actual on-disk size of your Analytic Workspace. This does
not subtract the reusable space within the Analytic Workspace.

set pages 500 lines 110
bre on REPORT;
comp sum lab "Total Disk:" of mb on REPORT;

col awname format a35 heading "Analytic Workspace"
col tablespace_name format a20 heading "Tablespace"
col mb format 999,999,990.00 heading "On Disk MB"

select dbal.owner||'.'||substr(dbal.table_name,4) awname,
sum(dbas.bytes)/1024/1024 as mb,
dbas.tablespace_name from dba_lobs dbal, dba_segments dbas
where dbal.column_name = 'AWLOB' and dbal.segment_name =
dbas.segment_name
group by dbal.owner, dbal.table_name, dbas.tablespace_name
order by dbal.owner, dbal.table_name;

Analytic Workspace On Disk MB Tablespace
----------------------------------- --------------- -------------------
SCOTT.TEST 24,197.00 USERS
SYS.AWCREATE 2.00 SYSAUX
SYS.AWCREATE10G 0.75 SYSAUX
SYS.AWMD 2.00 SYSAUX
SYS.AWREPORT 0.81 SYSAUX
SYS.AWXML 9.00 SYSAUX
SYS.EXPRESS 2.00 SYSAUX
---------------
Total Disk: 24,213.56