6 Replies Latest reply: Nov 11, 2009 5:10 AM by userxyz1234 RSS

    How to monitor the Database growth ???

    Nano_4612219
      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
          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
            -- 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
              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 ???
                Santosh Kumar
                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
                  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
                    http://blogs.oracle.com/myadav//2008/08/using_oem_want_to_know_databas.html