This discussion is archived
6 Replies Latest reply: Nov 11, 2009 3:10 AM by userxyz1234 RSS

How to monitor the Database growth ???

731537 Newbie
Currently Being Moderated
Hello Everyone,

I want to monitor Database Growth.
How to monitor the DB growth ?
OS : HP
DB : 10.2.0.1

Please help me.


Thenaks,
  • 1. Re: How to monitor the Database growth ???
    611948 Newbie
    Currently Being Moderated
    Take snapshot of your database size in different intervals. check this site, amcharts.com, it has got some templates for different kinda charts.. you may like it..
  • 2. Re: How to monitor the Database growth ???
    asifkabirdba Guru
    Currently Being Moderated
    -- total DB size

    select round((sum(bytes)/1048576/1024),2)
    from V$datafile;

    select round((sum(bytes)/1048576/1024),2)
    from V$tempfile;

    Take the sum of this two values which will be your total database size. Record this value daily/weekly/monthly basis and compare the difference.

    Hope this will help.


    Regards
    Asif kabir
  • 3. Re: How to monitor the Database growth ???
    Zeeshan BaiG Oracle ACE
    Currently Being Moderated
    u can create a OS script and schedule the above commands to email u on regular basis or u can store the result in db table for comparision the above queries.
  • 4. Re: How to monitor the Database growth ???
    515958 Pro
    Currently Being Moderated
    SQL> select sum(a.bytes+b.bytes)/1024/1024 "Total Size(MB)",sum(c.bytes)/1024/1024 "Used Space(MB)",sum(d.bytes)/1024/1024 "Free Space(MB)" from dba_data_files a,dba_temp_files b,dba_segments c,dba_free_space d;
    
    Total Size(MB)                   Used Space(MB)              Free Space(MB)
    --------------                   --------------              --------------
        1060137494                     528906                      28491086.3
    
    
    The Total Size includes the size of Temporary tablespaces also, which is excluded in the rest two figures.
    You may also think of using dba_extents instead of dba_segments.

    Regards,
    S.K.
  • 5. Re: How to monitor the Database growth ???
    Pavan DBA Expert
    Currently Being Moderated
    hope below scripts help you !

    column “Percent of Total Disk Usage” justify right format 999.99
    column “Space Used (MB)” justify right format 9,999,999.99
    column “Total Object Size (MB)” justify right format 9,999,999.99
    set linesize 150
    set pages 80
    set feedback off
    select * from (select to_char(end_interval_time, ‘MM/DD/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”
    from
    dba_hist_snapshot sn,
    dba_hist_seg_stat a,
    dba_objects b,
    dba_segments c
    where begin_interval_time > trunc(sysdate) – &days_back
    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’
    group by to_char(end_interval_time, ‘MM/DD/YY’))
    order by to_date(mydate, ‘MM/DD/YY’);

    Weekly growth of database
    set feedback off
    set pages 80
    set linesize 150
    spool /tmp/weekly_growth.txt
    ttitle “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”
    from
    dba_hist_snapshot sn,
    dba_hist_seg_stat a,
    dba_objects b,
    dba_segments c
    see code depot for full script
    where end_interval_time > trunc(sysdate) – &days_back
    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 = ‘&schema_name’
    and space_used_delta > 0;
    title “Total Disk Used by Object Type”
    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”
    from
    dba_hist_snapshot sn,
    dba_hist_seg_stat a,
    dba_objects b,
    dba_segments c
    see code depot for full script
    where end_interval_time > trunc(sysdate) – &days_back
    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);
    spool off
  • 6. Re: How to monitor the Database growth ???
    userxyz1234 Explorer
    Currently Being Moderated
    http://blogs.oracle.com/myadav//2008/08/using_oem_want_to_know_databas.html

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points