Re: Looking for Scripts for Database Month Growth / Tablespace Growth.

hi,

check whether following like query can be of your use..
you need to create a table say database_tablespace_growth_stats using say
below like query
and with insert and select you can create a job to insert the data

SQL> l
  1  SELECT (select name from v$database) dbname,
  2  to_char(sysdate,'DD-MOn-RR HH24:MI:SS') rundatetime,
  3  Total.name "Tablespace Name",
  4          nvl(Free_space, 0) Free_space,
  5          nvl(total_space-Free_space, 0) Used_space,
  6          total_space
  7   FROM
  8     (select tablespace_name, sum(bytes/1024/1024) Free_Space
  9        from sys.dba_free_space
 10       group by tablespace_name
 11     ) Free,
 12     (select b.name,  sum(bytes/1024/1024) TOTAL_SPACE
 13        from sys.v_$datafile a, sys.v_$tablespace B
 14       where a.ts# = b.ts#
 15       group by b.name
 16     ) Total
 17   WHERE Free.Tablespace_name(+) = Total.name
 18*  ORDER BY free_space
SQL> /
DBNAME   RUNDATETIME          Tablespace Name FREE_SPACE USED_SPACE
TOTAL_SPACE
-------- -------------------- --------------- ---------- ----------
-----------
ORA9I    20-JAN-11 19:01:40   XDB                  .1875    37.9375
38.125
ORA9I    20-JAN-11 19:01:40   EXAMPLE              .4375   137.6875
138.125
ORA9I    20-JAN-11 19:01:40   SYSTEM                6.25     483.75
490
ORA9I    20-JAN-11 19:01:40   TOOLS               9.9375      .0625
10
ORA9I    20-JAN-11 19:01:40   DRSYS              10.3125     9.6875
20
ORA9I    20-JAN-11 19:01:40   CWMLITE             10.625      9.375
20
ORA9I    20-JAN-11 19:01:40   ODM                10.6875     9.3125
20
ORA9I    20-JAN-11 19:01:40   USERS               24.875       .125
25
ORA9I    20-JAN-11 19:01:40   INDX               24.9375      .0625
25
ORA9I    20-JAN-11 19:01:40   DATA               49.9375      .0625
50
ORA9I    20-JAN-11 19:01:40   PERFSTAT          158.1875    91.8125
250
ORA9I    20-JAN-11 19:01:40   UNDOTBS1          348.6875    51.3125
400
12 rows selected.









>
>>>    Hi Everyone...
>>>
>>>    I take care of 4 - Oracle  10GR2 Database on Windows Platform.
>>>
>>>    I am looking for scripts or ways which can be implemented to monitor
>>>    the growth of the tablespaces and database..[Monthly,Yearly so on..]
>>>
>>>    Please can someone share with me the Growth Rate Monitoring Methods
>>>    so I can get it working out here.
>>>
>>>
>>>    Thank you,
>>>    Sheldon..
>>>
>>>
>>>
>>>
>


-- 
==============================
DO NOT FORGET TO SMILE TODAY
==============================

Other related posts: