Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Object Growth

Object Growth

From: Post, Ethan <epost_at_kcc.com>
Date: Wed, 12 Sep 2001 18:40:24 -0700
Message-ID: <F001.0038C8A0.20010912181518@fatcity.com> 






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.






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: epost_at_kcc.com

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: ListGuru_at_fatcity.com (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).


Received on Wed Sep 12 2001 - 20:40:24 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US