Operating System - HP-UX
1748177 Members
4260 Online
108758 Solutions
New Discussion юеВ

Re: Check each oracle user account on disk usage

 
SOLVED
Go to solution
Chris Fung
Frequent Advisor

Check each oracle user account on disk usage

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,
4 REPLIES 4
Yogeeraj_1
Honored Contributor
Solution

Re: Check each oracle user account on disk usage

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Yogeeraj_1
Honored Contributor

Re: Check each oracle user account on disk usage

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
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Brian Crabtree
Honored Contributor

Re: Check each oracle user account on disk usage

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
Steven E. Protter
Exalted Contributor

Re: Check each oracle user account on disk usage

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
Steven E Protter
Owner of ISN Corporation
http://isnamerica.com
http://hpuxconsulting.com
Sponsor: http://hpux.ws
Twitter: http://twitter.com/hpuxlinux
Founder http://newdatacloud.com