Just wanted to share a script I use for managing object growth. Create the table and run the procedure once per day. I usually have an access report based of this kind of stuff that prints me a nice pretty picture. Hope it helps someone. - Ethan Post - http://www.geocities.com/epost1 CREATE TABLE SEG_HIST ( owner VARCHAR2(30), segment_name VARCHAR2(81), partition_name VARCHAR2(30), segment_type VARCHAR2(17), tablespace_name VARCHAR2(30), start_date DATE DEFAULT TRUNC(SYSDATE), start_bytes NUMBER DEFAULT 0, last_bytes NUMBER DEFAULT 0, jan_bytes NUMBER DEFAULT 0, feb_bytes NUMBER DEFAULT 0, mar_bytes NUMBER DEFAULT 0, apr_bytes NUMBER DEFAULT 0, may_bytes NUMBER DEFAULT 0, jun_bytes NUMBER DEFAULT 0, jul_bytes NUMBER DEFAULT 0, aug_bytes NUMBER DEFAULT 0, sep_bytes NUMBER DEFAULT 0, oct_bytes NUMBER DEFAULT 0, nov_bytes NUMBER DEFAULT 0, dec_bytes NUMBER DEFAULT 0, updated DATE DEFAULT TRUNC(SYSDATE), CONSTRAINT pk_segment_stats PRIMARY KEY (owner, segment_name, partition_name, segment_type, tablespace_name)) / PROCEDURE segment_history IS CURSOR c_segments IS SELECT * FROM dba_segments WHERE segment_type IN ('TABLE', 'INDEX') AND owner NOT IN ('SYS', 'SYSTEM') AND bytes >= 10*1024*1024; l_record SEG_HIST%ROWTYPE; l_mth NUMBER; l_new_record BOOLEAN; l_updated DATE := SYSDATE; BEGIN FOR x IN c_segments LOOP l_new_record := FALSE; BEGIN SELECT * INTO l_record FROM seg_hist WHERE owner = x.owner AND segment_name = x.segment_name AND partition_name = nvl(x.partition_name, 'NULL') AND segment_type = x.segment_type AND tablespace_name = x.tablespace_name FOR UPDATE; EXCEPTION WHEN NO_DATA_FOUND THEN l_new_record := TRUE; WHEN OTHERS THEN dbms_output.put_line(dbms_utility.format_error_stack); END; IF l_new_record THEN INSERT INTO seg_hist ( owner, segment_name, partition_name, segment_type, tablespace_name, start_bytes, last_bytes, updated) VALUES (x.owner, x.segment_name, NVL(x.partition_name, 'NULL'), x.segment_type, x.tablespace_name, x.bytes, x.bytes, l_updated); ELSE l_mth := TO_NUMBER(TO_CHAR(SYSDATE, 'MM')); IF l_mth = 1 THEN l_record.jan_bytes := l_record.jan_bytes + (x.bytes-l_record.last_bytes); l_record.feb_bytes := 0; ELSIF l_mth = 2 THEN l_record.feb_bytes := l_record.feb_bytes + (x.bytes-l_record.last_bytes); l_record.mar_bytes := 0; ELSIF l_mth = 3 THEN l_record.mar_bytes := l_record.mar_bytes + (x.bytes-l_record.last_bytes); l_record.apr_bytes := 0; ELSIF l_mth = 4 THEN l_record.apr_bytes := l_record.apr_bytes + (x.bytes-l_record.last_bytes); l_record.may_bytes := 0; ELSIF l_mth = 5 THEN l_record.may_bytes := l_record.may_bytes + (x.bytes-l_record.last_bytes); l_record.jun_bytes := 0; ELSIF l_mth = 6 THEN l_record.jun_bytes := l_record.jun_bytes + (x.bytes-l_record.last_bytes); l_record.jul_bytes := 0; ELSIF l_mth = 7 THEN l_record.jul_bytes := l_record.jul_bytes + (x.bytes-l_record.last_bytes); l_record.aug_bytes := 0; ELSIF l_mth = 8 THEN l_record.aug_bytes := l_record.aug_bytes + (x.bytes-l_record.last_bytes); l_record.sep_bytes := 0; ELSIF l_mth = 9 THEN l_record.sep_bytes := l_record.sep_bytes + (x.bytes-l_record.last_bytes); l_record.oct_bytes := 0; ELSIF l_mth = 10 THEN l_record.oct_bytes := l_record.oct_bytes + (x.bytes-l_record.last_bytes); l_record.nov_bytes := 0; ELSIF l_mth = 11 THEN l_record.nov_bytes := l_record.nov_bytes + (x.bytes-l_record.last_bytes); l_record.dec_bytes := 0; ELSIF l_mth = 12 THEN l_record.dec_bytes := l_record.dec_bytes + (x.bytes-l_record.last_bytes); l_record.jan_bytes := 0; END IF; UPDATE seg_hist SET last_bytes = x.bytes, jan_bytes = l_record.jan_bytes, feb_bytes = l_record.feb_bytes, mar_bytes = l_record.mar_bytes, apr_bytes = l_record.apr_bytes, may_bytes = l_record.may_bytes, jun_bytes = l_record.jun_bytes, jul_bytes = l_record.jul_bytes, aug_bytes = l_record.aug_bytes, sep_bytes = l_record.sep_bytes, oct_bytes = l_record.oct_bytes, nov_bytes = l_record.nov_bytes, dec_bytes = l_record.dec_bytes, updated = trunc(sysdate) WHERE owner = x.owner AND segment_name = x.segment_name AND partition_name = nvl(x.partition_name, 'NULL') AND segment_type = x.segment_type AND tablespace_name = x.tablespace_name; END IF; END LOOP; DELETE FROM seg_hist WHERE updated < l_updated; COMMIT; EXCEPTION WHEN OTHERS THEN dbms_output.put_line(dbms_utility.format_error_stack); END; / ------------------------------------------------------------------------------ This e-mail is intended for the use of the addressee(s) only and may contain privileged, confidential, or proprietary information that is exempt from disclosure under law. If you have received this message in error, please inform us promptly by reply e-mail, then delete the e-mail and destroy any printed copy. Thank you. ============================================================================== -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Post, Ethan INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
