Query help

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Thu, 12 Mar 2015 07:55:38 -0500
Message-Id: <A57ADBCA-6E22-4F51-B93C-49B363710835_at_gmail.com>

This is a learning experience for me. I wrote the query below to pull sizing trends at the database level. Note it uses a with. I think it can be written with a single select and a roll up clause, or perhaps another function I am not aware of. Any ideas?

with group1 as (select
to_char(s.begin_interval_time,'yyyy-mm-dd') get_date, v.name ts_name,
(round(max((t.tablespace_size*8192))/1024/1024/1024,2)) size_gb,
(round(max((tablespace_usedsize*8192))/1024/1024/1024,2)) used_gb
from v$tablespace v, dba_hist_snapshot s, dba_hist_tbspc_space_usage t where t.tablespace_id=v.ts#
and t.snap_id=s.snap_id
group by to_char(s.begin_interval_time,'yyyy-mm-dd'), v.name) select get_date, sum(size_gb) tot_size,sum(used_gb) used_size from group1
group by get_date
order by get_date

Sent from my iPad--
http://www.freelists.org/webpage/oracle-l Received on Thu Mar 12 2015 - 13:55:38 CET