Sign in or 
Share this
Simple Space Report with OLAP Multidimensional Datatype Information
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
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
olapdba |
Latest page update: made by olapdba
, Jan 24 2008, 4:07 PM EST
(about this update
About This Update
Edited by olapdba
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.
