select total.tablespace_name tsname, –count(free.bytes) nfrags, nvl(max(free.bytes)/1024/1024,0) “Total(gb)”, total.bytes/1024/1024/1024 “Used(gb)”, –nvl(sum(free.bytes)/1024,0) avasiz, nvl(max(free.bytes)/1024/1024,0)-total.bytes/1024/1024/1024 “Freespace(gb)”, (1-nvl(sum(free.bytes),0)/total.bytes)*100 “% used” from dba_data_files total, dba_free_space free where total.tablespace_name = free.tablespace_name(+) and total.file_id=free.file_id(+) group by total.tablespace_name,total.bytes
“Total Disk Used”
select sum(space_used_delta) / 1024 / 1024 “Space used (M)”, sum(c.bytes) / 1024 / 1024 “Total Schema Size (M)”, round(sum(space_used_delta) / sum(c.bytes) * 100, 2) || ‘%’ “Percent of Total Disk Usage”, c.owner from dba_hist_snapshot sn, dba_hist_seg_stat a, dba_objects b, dba_segments c where end_interval_time > trunc(sysdate) – 31 and sn.snap_id = a.snap_id and b.object_id = a.obj# and b.owner = c.owner and b.object_name = c.segment_name –and c.owner = ‘R12’ and space_used_delta > 0 group by c.owner
— “Total Disk Used by Object Type” — 31 days ( monthly growth )
select c.segment_type, sum(space_used_delta) / 1024 / 1024 “Space used (M)”, sum(c.bytes) / 1024 / 1024 “Total Space (M)”, round(sum(space_used_delta) / sum(c.bytes) * 100, 2) || ‘%’ “Percent of Total Disk Usage”, c.owner from dba_hist_snapshot sn, dba_hist_seg_stat a, dba_objects b, dba_segments c where end_interval_time > trunc(sysdate) – 31 and sn.snap_id = a.snap_id and b.object_id = a.obj# and b.owner = c.owner and b.object_name = c.segment_name and space_used_delta > 0 –and c.owner = ‘&schema_name’ group by rollup(segment_type),c.owner
— “Percent of Total Disk Usage” justify right format 999.99 — “Space Used (MB)” justify right format 9,999,999.99 — “Total Object Size (MB)” justify right format 9,999,999.99
select * from (select to_char(end_interval_time, ‘DD/MM/YY’) mydate, sum(space_used_delta) / 1024 / 1024 “Space used (MB)”, –avg(c.bytes) / 1024 / 1024 “Total Object Size (MB)”, –round(sum(space_used_delta) / sum(c.bytes) * 100, 2) “Percent of Total Disk Usage”, c.tablespace_name from dba_hist_snapshot sn, dba_hist_seg_stat a, dba_objects b, dba_segments c where begin_interval_time > trunc(sysdate) – 1 and sn.snap_id = a.snap_id and b.object_id = a.obj# and b.owner = c.owner and b.object_name = c.segment_name –and c.segment_name = ‘&segment_name’ –and c.owner in (‘R12′,’DAS_ODS’,’DAS_DW’,’OWF_MGR’,’OWBSYS’) and c.tablespace_name in (‘DATA’,’INDEXES’) group by to_char(end_interval_time, ‘DD/MM/YY’),c.tablespace_name) order by to_date(mydate, ‘DD/MM/YY’); select * from (select to_char(end_interval_time, ‘DD/MM/YY’) mydate, c.tablespace_name, sum(space_used_delta) / 1024 / 1024 “Space used (MB)” –avg(c.bytes) / 1024 / 1024 “Total Object Size (MB)”, –round(sum(space_used_delta) / sum(c.bytes) * 100, 2) “Percent of Total Disk Usage”, from dba_hist_snapshot sn, dba_hist_seg_stat a, dba_objects b, dba_segments c where begin_interval_time > trunc(sysdate) – 1 and sn.snap_id = a.snap_id and b.object_id = a.obj# and b.owner = c.owner and b.object_name = c.segment_name –and c.segment_name = ‘&segment_name’ –and c.owner in (‘R12′,’DAS_ODS’,’DAS_DW’,’OWF_MGR’,’OWBSYS’) and c.tablespace_name in (‘DATA’,’INDEXES’) group by to_char(end_interval_time, ‘DD/MM/YY’),c.tablespace_name) order by to_date(mydate, ‘DD/MM/YY’);