Using the ROLLUP Function

[tekpr00]

Hello All,

I know I have asked you a question similar to below in the past, but the request keeps changing on me.

Here is another attempt at getting a weekly Growth of tablesapces and use ROLLUP function to see the weekly total of space usage by schema owner. Below is proposed output


Week TABLESPACE OWNER Space Total-Schema Percent of Total Disk Usage
Ending NAME Used Size
--------- --------------- ------ ---------------- ---------------------------
SWT_DATA SWT
SWT_DATA SWT
SWT_INDEX SWT
SWT_DATA SWT
Weekly Total
DRSYS WKSYS
DRSYS WKSYS
DRSYS WKSYS
Weekly Total

Here is my code:

select
TO_CHAR(SYSDATE,'MON DD, YYYY HH24:MI:SS') "Week Ending", -- to bt executed every Sunday
sum(space_used_delta) / 1024 / 1024 "Space used (M)",
c.owner,
sum(c.bytes) / 1024 / 1024 "Total Schema Size (M)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) || '%' "Percent of Total Disk Usage"
from
dba_hist_snapshot sn,
dba_hist_seg_stat a,
dba_objects b,
dba_segments c
where end_interval_time > trunc(sysdate) - 7
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.owner not in ('SYSTEM', 'XDB', 'SYS', 'TSMSYS', 'MDSYS', 'EXFSYS', 'WMSYS', 'ORDSYS', 'OUTLN', 'DBSNMP')
GROUP BY ROLLUP((TO_CHAR(SYSDATE,'MON DD, YYYY HH24:MI:SS'),c.owner)
order by (TO_CHAR(SYSDATE,'MON DD, YYYY HH24:MI:SS'),
c.owner)

I was wondering if anyone could help with this.

Thanks a lot.

 

[tekpr00]

I have refined this code a bit...
Here is what I am using now

select * from (select to_char(end_interval_time, 'MM/DD/YY') mydate, 
c.owner "Owner or User",
sum(space_used_delta) / 1024 / 1024 "Space used (MB)",
avg(c.bytes) / 1024 / 1024 "Total Object Size (MB)",
round(sum(space_used_delta) / sum(c.bytes) * 100, 2) "Percent of Total Disk Usage"
from
   dba_hist_snapshot sn,
   dba_hist_seg_stat a,
   dba_objects b,
   dba_segments c
where begin_interval_time > trunc(sysdate) - 7
and sn.snap_id = a.snap_id
and b.object_id = a.obj#
and b.owner = c.owner
and b.object_name = c.segment_name
and c.owner not in ('SYSAUX','SYSTEM', 'XDB', 'SYS', 'TSMSYS', 'MDSYS', 'EXFSYS', 'WMSYS', 'ORDSYS', 'OUTLN', 'DBSNMP')
group by rollup (to_char(end_interval_time, 'MM/DD/YY'), space_used_delta, c.owner))
--order by to_date(mydate, 'MM/DD/YY'), c.owner
order by 1,2

Here is the result:

06/11/13	MIWT	0	.080357143	0
06/11/13		0	.080357143	0
06/11/13		0	.080357143	0
06/12/13	MIWT	0	.08125	0
06/12/13		0	.08125	0
06/12/13		0	.08125	0
06/17/13	MIWT	0	1.53125	0
06/17/13	MIWT	.01171875	.0625	18.75
06/17/13		0	1.53125	0
06/17/13		.01171875	.0625	18.75
06/17/13		.01171875	1.04166667	.38
06/18/13	MIWT	0	.0625	0
06/18/13	MIWT	.01171875	.0625	9.38
06/18/13	MIWT	.061718941	.0625	98.75
06/18/13	MIWT	.023212433	.0625	37.14
06/18/13	MIWT	.014687538	.0625	23.5
06/18/13	MIWT	.013750076	.0625	22
06/18/13	MIWT	.009765625	.0625	15.63
06/18/13	MIWT	-0.0019474	.0625	-3.12
06/18/13	MIWT	.008828163	.0625	14.13
06/18/13	MIWT	.006875038	.0625	11
06/18/13	MIWT	.005842209	.0625	9.35
06/18/13	MIWT	.01171875	.0625	6.25
06/18/13	MIWT	.003894806	.0625	6.23
06/18/13		.008828163	.0625	14.13
06/18/13		0	.0625	0
06/18/13		-0.0019474	.0625	-3.12
06/18/13		.170064926	.0625	14.32
06/18/13		.006875038	.0625	11
06/18/13		.013750076	.0625	22
06/18/13		.01171875	.0625	9.38
06/18/13		.014687538	.0625	23.5
06/18/13		.005842209	.0625	9.35
06/18/13		.023212433	.0625	37.14
06/18/13		.01171875	.0625	6.25
06/18/13		.009765625	.0625	15.63
06/18/13		.003894806	.0625	6.23
06/18/13		.061718941	.0625	98.75
		.181783676	.135869565	2.91

As you can see I am getting duplicates.
I will like to cut the duplicates and sum by "Percent of Total Disk Usage" on Weekly basis.
Any help will be appreciated.

 

[LKBrwnDBA]

You should not re-invent the wheel.
How about using the "canned" storage reports provided by Enterprise Manager dbConsole or Grid 11g or Cloud 12g?
If they are not pretty enough, just load them into a speadsheet and get beautiful graphics!.





----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[tekpr00]

We are not licensed to use that.

 

[LKBrwnDBA]

EM dbConsole is free with database.
Unfortunately if you do not have it installed, you have no statistics on which to generate the reports.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb