Hi Stephane, I would try this out and get back to you on it... Its great to know people are really sharing their expertise.. On Tue, Jan 18, 2011 at 6:03 PM, Stephane Faroult <sfaroult@xxxxxxxxxxxx>wrote: > Sheldon, > > I happen to be working on a video tutorial and I'm just on something > related. IMHO the best thing to do is to relate the size of tables (and > their associated indexes) to the number of lines, because the number of > lines is often the only thing that makes sense business-wise and for which > people are willing to venture future figures. > > What I suggest is that you create as system something such as > > create table volume_history( > as_of date not null, > table_owner varchar2(30) not null, > table_name varchar2(30) not null, > num_rows number, > K_table number, > K_index number, > constraint volume_history_pk > primary key (table_owner, table_name, as_of)) > tablespace sysaux > / > > Then connect as SYS, grant SELECT on DBA_TABLES, DBA_SEGMENTS and > DBA_INDEXES to SYSTEM (otherwise procedure creation will fail) > and create (as SYSTEM again) something like this (I assume you have no lobs > - otherwise you should involve DBA_LOBS in the query - you can also remove > other schemas than SYS and SYSTEM - or keep them!): > > create or replace procedure record_volume > as > begin > insert into volume_history(as_of, table_owner, table_name, > num_rows, k_table, k_index) > select trunc(sysdate), > table_owner, > table_name, > sum(num_rows) num_rows, > round(sum(table_size)/1024) K_table, > round(sum(index_size)/1024) K_index > from (select t.owner table_owner, > t.table_name, > t.num_rows, > s.bytes table_size, > 0 index_size > from dba_tables t > inner join dba_segments s > on s.owner = t.owner > and s.segment_name = t.table_name > where t.owner not in ('SYSTEM', 'SYS') > union all > select i.table_owner, > i.table_name, > to_number(null) num_rows, > 0 table_size, > s.bytes index_size > from dba_indexes i > inner join dba_tables t > on t.owner = i.table_owner > and t.table_name = i.table_name > inner join dba_segments s > on s.owner = i.owner > and s.segment_name = i.index_name > where t.owner not in ('SYSTEM', 'SYS')) > group by table_owner, table_name; > commit; > end; > / > > > Use DBMS_SCHEDULER to run it at a frequency that is equal to or greater > than stats collection (once a week is more than enough, IMHO) > and you have some data that you can export to a CSV file from time to time > to draw the graphics that usually enrapture management. > > Of course, you can collect data about tablespace size etc. in the very same > way. And if you add the database name to the data, you can even consolidate > everything at one place if needed. > > Hope that helps, > > > Stephane Faroult > RoughSea Ltd <http://www.roughsea.com> > Konagora <http://www.konagora.com> > RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd> > > On 01/18/2011 01:08 PM, Sheldon Quinny wrote: > > 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.. > > >