topic Re: Check each oracle user account on disk usage in Operating System - HP-UX

Check each oracle user account on disk usage

Chris Fung — Thu, 23 Jan 2003 05:50:47 GMT

Hi all,

Any idea to check the disk usage consumed by each user account ?

I am thinking of checking the dba_segment view "select owner, segment_type, sum(bytes) from dba_segments group by owner, segment_type;"

However, it will not display those users without database object created .....therefore I start thinking of correlate the dba_segment view with dba_users view....

Any idea to correlate them ?

Thanks,

Chris,

Re: Check each oracle user account on disk usage

Yogeeraj_1 — Thu, 23 Jan 2003 07:40:19 GMT

hi chris,

can you try the script below, to see if you are getting the desired output?

select decode(lag(username) over (partition by username order by segment_type), username, null, username) Owner, segment_type, sum(bytes)
from dba_segments, dba_users
where username=owner(+)
group by username,owner,segment_type;

hope this helps!

Best Regards
Yogeeraj

Re: Check each oracle user account on disk usage

Yogeeraj_1 — Thu, 23 Jan 2003 07:42:46 GMT

hi again,

a better formatted one would be:

column MB format 9,999.99
select decode(lag(username) over (partition by username order by segment_type), username, null, username) Owner, segment_type, sum(bytes)/1024/1024 MB
from dba_segments, dba_users
where username=owner(+)
group by username,owner,segment_type;

and will display the size in MB instead of bytes

Hope this helps!

Best Regards
yogeeraj

Re: Check each oracle user account on disk usage

Brian Crabtree — Thu, 23 Jan 2003 21:19:30 GMT

You could also do something like this:

select username,nvl(used_space,'0') from
(select owner,sum(bytes) used_space from
dba_segments group by owner) b, dba_users a
where a.username = b.owner(+);

Brian

Re: Check each oracle user account on disk usage

Steven E. Protter — Fri, 24 Jan 2003 01:11:20 GMT

There are a ton of good scripts in here.

http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x4177ef70e827d711abdc0090277a778c,00.html

I believe one or more of them might be useful.

P