Re: Looking for Scripts for Database Month Growth / Tablespace Growth.
- From: Sheldon Quinny <sheldonquinny@xxxxxxxxx>
- To: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- Date: Wed, 19 Jan 2011 08:18:30 +0300
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..
>
>
>
Other related posts:
