Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

How and what are all the segments are using oracle size

2684285Oct 15 2015 — edited Oct 15 2015

Dear Experts,

Operating System : Windows server 2012

Oracle version : 11.2.0.1.0

I need to get size of the database as user wise and table wise

when i check both of two tablespaces called Users and user_idx  as user wise it is showing as used size as 450 Gb.

I used following query in the same instance as table wise.

select owner,segment_name,sum(bytes)/1024 "Size in KB", sum(bytes)/1024/1024 "Size in MB" from dba_segments where owner not in

('SYS','SYSTEM','DBSNMP','OUTLN','SYSMAN','EXFSYS','CTXSYS','XDB','ORDDATA','SCOTT','APEX_030200','MDSYS','OLAPSYS') group by owner,segment_name;

the total size is showing as 20 Gb only.

So, When i checked both tablespace size as userwise it is showing 450 Gb but when i checked same as segmentwise using above query it is showing 20 gb.

My Question is

Why huge Size gap between user wise size and segment wise size. Actually all Each user has set of segments(tables,index..)

Is there any idea or Document to read and understand how oracle instance are using hdd size then please share. It help me more

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 12 2015
Added on Oct 15 2015
6 comments
1,194 views