Recording Segment Growth History

Keeping a history table to record the growth of Segments over time.

----------------------------------------------
-- TCCDBA user creation --
----------------------------------------------

create user tccdba identified by ???????
default tablespace users
temporary tablespace temp;

grant create session to tccdba;
grant dba to tccdba;
grant select on dba_segments to tccdba;

----------------------------------------------
-- segment growth history table --
----------------------------------------------

create table seg_growth_hist
( sgh_id number, sample_date date,
segment_own varchar2(30),
segment_name VARCHAR2(81),
segment_type VARCHAR2(18),
segment_size number);

create unique index seg_growth_hist$sgh_id
on seg_growth_hist(sgh_id);

alter table seg_growth_hist
add CONSTRAINT seg_growth_hist_sgh_id_PK
primary key (sgh_id);

create index seg_growth_hist$sample_date
on seg_growth_hist(sample_date);

create sequence seg_growth_hist_seq
start with 1 increment by 1;

----------------------------------------------
-- --
-- Create schedule job for seg growth hist --
-- --
----------------------------------------------

create or replace PROCEDURE seg_growth_hist_proc AS
BEGIN
insert into seg_growth_hist
(sgh_id, sample_date, segment_own,
segment_name, segment_type, segment_size)
select seg_growth_hist_seq.nextval, sysdate, sgh.*
from (select owner, segment_name, segment_type,
sum(bytes) from sys.dba_segments where owner in ('')
group by owner, segment_name, segment_type )sgh;
--
commit;
--
END;
/


BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'seg_growth_hist_job',
job_type => 'STORED_PROCEDURE',
job_action => 'tccdba.seg_growth_hist_proc',
start_date => '27-SEP-10 11.45.00 PM',
repeat_interval => 'FREQ=DAILY;INTERVAL=1',
end_date => null,
comments => 'seg growth hist');
END;
/

BEGIN
DBMS_SCHEDULER.ENABLE ('seg_growth_hist_job');
END;
/

Sample query for history.

select sght.sample_date, sght.segment_name, sght.segment_type, sght.segment_size-sghy.segment_size growth, sght.segment_size cur_size
from seg_growth_hist sght,
(select sample_date, segment_name, segment_type, segment_size
from seg_growth_hist
where trunc(sample_date) = trunc(sysdate) -1) sghy
where sght.segment_name = sghy.segment_name
and sght.segment_type = sghy.segment_type
and trunc(sght.sample_date) = trunc(sysdate);

******************************************
keywords: segment growth history
******************************************
rdbms version: 11.2.0.1
******************************************