CREATE SEQUENCE FPA_SNAP_ID START WITH 1 INCREMENT BY 1;

CREATE TABLE FPA_HIST_SEG_SIZE (
	SNAP_ID			NUMBER,
	SNAP_DATE		date,
	OWNER			VARCHAR2(30),
	SEGMENT_NAME		VARCHAR2(81),
	SEGMENT_TYPE		VARCHAR2(18),
	BYTES			NUMBER
) tablespace users
;

CREATE OR REPLACE PROCEDURE FPA_INSERT_HIST_SEG_SIZE IS
	SNAP	FPA_HIST_SEG_SIZE.SNAP_ID%TYPE;
BEGIN
	SELECT FPA_SNAP_ID.nextval into SNAP from dual;
	insert into FPA_HIST_SEG_SIZE
		select SNAP, SYSDATE, owner, segment_name, segment_type, BYTES
		from DBA_SEGMENTS where OWNER NOT LIKE 'SYS%' order by segment_name;
	commit;
END FPA_INSERT_HIST_SEG_SIZE;
/


CREATE OR REPLACE PROCEDURE FPA_DELETE_HIST_SEG_SIZE IS
BEGIN
	delete FPA_HIST_SEG_SIZE where snap_date < sysdate-366;
	delete FPA_HIST_SEG_SIZE where snap_date < sysdate-120 and mod(SNAP_ID,7) <> 0;
	commit;
END FPA_DELETE_HIST_SEG_SIZE;
/