Re: Looking for Scripts for Database Month Growth / Tablespace Growth.
- From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
- To: sheldonquinny@xxxxxxxxx
- Date: Tue, 18 Jan 2011 16:03:09 +0100
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:
