Re: Query help
- From: "Mladen Gogala" <dmarc-noreply@xxxxxxxxxxxxx> (Redacted sender "mgogala@xxxxxxxxx" for DMARC)
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 13 Mar 2015 13:58:22 -0400
On 03/12/2015 08:55 AM, Andrew Kerber wrote:
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--
//www.freelists.org/webpage/oracle-l
I get the best results by using the following form:
with group1 as (select /*+ materialize parallel(t,2) ordered */
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 dba_hist_tbspc_space_usage t, v$tablespace v, dba_hist_snapshot s
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
--
Mladen Gogala
Oracle DBA
http://mgogala.freehostia.com
--
//www.freelists.org/webpage/oracle-l
Other related posts: