INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Using the ROLLUP Function

Using the ROLLUP Function

(OP)
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.

RE: Using the ROLLUP Function

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

CODE -->

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:

CODE -->

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.

RE: Using the ROLLUP Function


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!.
noevil


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

RE: Using the ROLLUP Function

(OP)
We are not licensed to use that.

RE: Using the ROLLUP Function


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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close