Know the size of Each schema contain ....

[Ramana.Sv]

Hi,
we have HP9000 server(B.11.11),in that we have ORACLE 10G, only one DATABASE, in that different schemas.
i want to know the size of Each schema contain.


Thanks
Ramana

[klb]

Your best bet is to go to http://tahiti.oracle.com and read up on how to work with Oracle.

In the mean time some hints for you:

DBA_SEGMENTS columns of interest:
OWNER, SEGMENT_NAME, BYTES

-klb

[Yogeeraj_1]

hi Ramana,

You can try something similar to this:

admin@mydb.mu>r
1 select t.owner, sum(s.bytes)/1024/1024 MBYTE
2 from dba_tables t, dba_segments s
3 where t.owner = s.owner
4 and t.table_name = s.segment_name
5 and t.owner not in ('SYS','SYSTEM','OUTLN')
6 and s.bytes > 50000000
7 group by t.owner
8* order by MBYTE desc

OWNER MBYTE
______________________________ ____________
MORIS1 19,278
RODRIGU 5,433
DIEGO_01 127

Elapsed: 00:00:00.42
admin@mydb.mu>

heop this helps!

kind regards
Yogeeraj

[Volker Borowski]

Hi,

I would use dba_segments only and group by SEGMENT TYPE in addition, to get Indexes in addition (i have a 100GB table that has 290GB Indexes assigned to it !)

select
owner,segment_type,
sum(bytes)/1024/1024/1024 as GB
from dba_segments
group by
owner,segment_type
order by 1,2,3;

Shows you common used Storage (i.e. rollback/undo ans SYS objects) in addition.

Watch it, there is a bug in 10.2.0.2 which does not show the truth in BYTES or BLOCKS from dba_segments in some cases, even if stats are calculated.

Volker