Version User Scope of changes
Jan 24 2008, 4:07 PM EST (current) olapdba
Jan 24 2008, 4:07 PM EST olapdba

Changes

Key:  Additions   Deletions
set lines 110
col awspc format 9990.90 hea 'CONSUMED|AWs (GB)'
col ttspc format 9990.90 hea 'CONSUMED|TTL (GB)'
col alprm format 99990.90 hea 'ALLOCATED|PERM (GB)'
col altmp format 99990.90 hea 'ALLOCATED|TEMP (GB)'
col exprm format 99990.90 hea 'EXPANSIVE|PERM (GB)'
col extmp format 99990.90 hea 'EXPANSIVE|TEMP (GB)'
select a.gb awspc, b.gb ttspc, c.gb alprm, d.gb altmp, e.gb exprm, f.gb extmp
from (select round(sum(bytes)/1024/1024/1024,2) gb from dba_lobs l, dba_segments s
where column_name = 'AWLOB' and s.segment_name=l.segment_name) a,
(select round(sum(bytes)/1024/1024/1024,2) gb from dba_extents) b,
(select round(sum(bytes)/1024/1024/1024,2) gb from dba_data_files) c,
(select round(sum(bytes)/1024/1024/1024,2) gb from dba_temp_files) d,
(select round(sum(maxbytes)/1024/1024/1024,2) gb from dba_data_files) e,
(select round(sum(maxbytes)/1024/1024/1024,2) gb from dba_temp_files) f;

CONSUMED CONSUMED ALLOCATED ALLOCATED EXPANSIVE EXPANSIVE
AWs (GB) TTL (GB) PERM (GB) TEMP (GB) PERM (GB) TEMP (GB)
-------- -------- --------- --------- --------- ---------
... 59.88..152.99 ...186.94......0.88...6691.85 ...288.00

/* OR YOU COULD LOOK AT IT THIS WAY */

set lines 80 feed off
col mb for a55 hea "Instance Space Consumption"

select ' Consumed by AW (MB): '||round(sum(bytes)/1024/1024,2) mb from dba_lobs l, dba_segments s
where column_name = 'AWLOB' and s.segment_name=l.segment_name union
select ' Consumed Total (MB): '||round(sum(bytes)/1024/1024,2) mb from dba_extents union
select ' Allocated Perm (MB): '||round(sum(bytes)/1024/1024,2) mb from dba_data_files union
select ' Allocated Temp (MB): '||round(sum(bytes)/1024/1024,2) mb from dba_temp_files union
select ' Expansive Perm (MB): '||round(sum(maxbytes)/1024/1024,2) mb from dba_data_files union
select ' Expansive Temp (MB): '||round(sum(maxbytes)/1024/1024,1) mb from dba_temp_files;

Instance Space Consumption
-------------------------------------------------------
Allocated Perm (MB): 191424
Allocated Temp (MB): 900
Consumed Total (MB): 156656.5
Consumed by AW (MB): 61318.63
Expansive Perm (MB): 6852459.09
Expansive Temp (MB): 294911.9