web/modules/dobby/Dobby/Reporting.pm | 42 +++++++++++++++--------------------
1 file changed, 19 insertions(+), 23 deletions(-)
New commits:
commit 943f56f1d333cc51159cc1803831da82c13a741d
Author: Michael Mraka <michael.mraka(a)redhat.com>
Date: Tue Dec 21 14:23:17 2010 +0100
664487 - fixed space report query
diff --git a/web/modules/dobby/Dobby/Reporting.pm b/web/modules/dobby/Dobby/Reporting.pm
index 4cc974a..331e951 100644
--- a/web/modules/dobby/Dobby/Reporting.pm
+++ b/web/modules/dobby/Dobby/Reporting.pm
@@ -25,33 +25,29 @@ sub tablespace_overview {
my $dbh = $dobby->sysdba_connect;
my $query = <<EOQ;
-SELECT DT.tablespace_name NAME, DT.extent_management, DT.contents,
- DFS.bytes FREE_BYTES,
- DUS.bytes USED_BYTES,
- nvl(DTS.bytes,0) TOTAL_BYTES
- FROM (SELECT tablespace_name,
- SUM(bytes) bytes
- FROM dba_free_space
- GROUP BY tablespace_name) DFS,
- (SELECT u.tablespace,
- sum(u.blocks* p.value) bytes
- FROM v\$tempseg_usage u,
- v\$parameter p
- WHERE p.name = 'db_block_size'
- GROUP BY u.tablespace) DUS,
- (SELECT tablespace_name,
- SUM(bytes) bytes
+SELECT DT.tablespace_name as NAME, DT.extent_management, DT.contents,
+ case DT.contents when 'TEMPORARY' then
DF.total_bytes-NVL(DTF.used_bytes,0) else NVL(DFS.free_bytes,0) end as free_bytes,
+ case DT.contents when 'TEMPORARY' then NVL(DTF.used_bytes,0) else
DF.total_bytes-NVL(DFS.free_bytes,0) end as used_bytes,
+ DF.total_bytes
+ FROM dba_tablespaces DT
+ JOIN (SELECT tablespace_name, sum(bytes) as total_bytes
FROM dba_data_files
GROUP BY tablespace_name
UNION
- SELECT tablespace_name,
- SUM(bytes) bytes
+ SELECT tablespace_name, SUM(bytes) as total_bytes
FROM dba_temp_files
- GROUP BY tablespace_name) DTS,
- DBA_TABLESPACES DT
- WHERE DFS.tablespace_name (+) = DTS.tablespace_name
- AND DTS.tablespace_name = DT.tablespace_name
- AND DUS.tablespace (+) = DTS.tablespace_name
+ GROUP BY tablespace_name) DF
+ on DT.tablespace_name = DF.tablespace_name
+ LEFT JOIN (SELECT tablespace_name, SUM(bytes) as free_bytes
+ FROM dba_free_space
+ GROUP BY tablespace_name) DFS
+ on DT.tablespace_name = DFS.tablespace_name
+ LEFT JOIN (SELECT u.tablespace, sum(u.blocks* p.value) as used_bytes
+ FROM v\$tempseg_usage u,
+ v\$parameter p
+ WHERE p.name = 'db_block_size'
+ GROUP BY u.tablespace) DTF
+ on DT.tablespace_name = DTF.tablespace
ORDER BY DT.tablespace_name
EOQ
Show replies by date