Monthly Archives: September 2014

Oracle Schemea Objects are taking up most space

How much space does the Oracle Schema take in Database?  How many objects are in each Oracle Schema?

Show Schema Object per Database and total size in megabytes

set lines 200
set pages 999
col "Owner" format a15
col "MB's" format 999,999,999
col "Obj" format 999,999,999
select    obj.owner "Owner"
,    obj_cnt "Objects"
,    decode(seg_size, NULL, 0, seg_size) "MB's"
from     (select owner, count(*) obj_cnt from dba_objects group by owner) obj
,    (select owner, ceil(sum(bytes)/1024/1024) seg_size
    from dba_segments group by owner) seg
where     obj.owner  = seg.owner(+)
order    by 3 desc ,2 desc, 1
/

Show Schema Object per Database and total size in megabytes

set lines 200
set pages 999
col "Owner" format a15
col "GB's" format 999,999,999
col "Obj" format 999,999,999
select    obj.owner "Owner"
,    obj_cnt "Objects"
,    decode(seg_size, NULL, 0, seg_size) "GB's"
from     (select owner, count(*) obj_cnt from dba_objects group by owner) obj
,    (select owner, ceil(sum(bytes)/1024/1024/1024) seg_size
    from dba_segments group by owner) seg
where     obj.owner  = seg.owner(+)
order    by 3 desc ,2 desc, 1
/

Check Oracle Database Size Megabytes or Gigabytes

Query to Check the Oracle Database Size in Megabytes or Gigaybtes?

Database Size in Megabytes

set lines 200
set pages 999
col "DBSize" format a10
col "Free" format a10
col "Used" format a10
select    round(sum(used.bytes) / 1024 / 1024  ) || ' MB' "DBSize"
,    round(sum(used.bytes) / 1024 / 1024  ) - 
    round(free.p / 1024 / 1024 ) || ' MB' "Used"
,    round(free.p / 1024 / 1024 ) || ' MB' "Free"
from    (select    bytes
    from    v$datafile
    union    all
    select    bytes
    from     v$tempfile
    union     all
    select     bytes
    from     v$log) used
,    (select sum(bytes) as p
    from dba_free_space) free
group by free.p
/

 

Database Size in Gigabytes

set lines 200
set pages 999
col "DBSize" format a10
col "Free" format a10
col "Used" format a10
select    round(sum(used.bytes) / 1024 / 1024 / 1024  ) || ' GB' "DBSize"
,    round(sum(used.bytes) / 1024 / 1024 / 1024  ) - 
    round(free.p / 1024 / 1024 / 1024 ) || ' GB' "Used"
,    round(free.p / 1024 / 1024 / 1024 ) || ' GB' "Free"
from    (select    bytes
    from    v$datafile
    union    all
    select    bytes
    from     v$tempfile
    union     all
    select     bytes
    from     v$log) used
,    (select sum(bytes) as p
    from dba_free_space) free
group by free.p
/