Simpler Navigation coming for Servers and Operating Systems
Coming soon: a much simpler Servers and Operating Systems section of the Community. We will combine many of the older boards, and you won't have to click through so many levels to get at the information you need. If you are looking for an older board and do not find it, check the consolidated boards, as the posts are still there.
Databases
cancel
Showing results for 
Search instead for 
Did you mean: 

Know the size of Each schema contain ....

Ramana.Sv
Frequent Advisor

Know the size of Each schema contain ....

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
3 REPLIES
klb
Valued Contributor

Re: Know the size of Each schema contain ....


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
Honored Contributor

Re: Know the size of Each schema contain ....

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

Re: Know the size of Each schema contain ....

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