Gather historic information about table size and row count

Often the support need to know which tables are growing fast and which are stable. Out of the Box, Oracle don’t provide such a statistic on object level. Classic monitoring tools collect this information only on the database-, tablespace- and datafile-level, but not on object level.

A small script helps you to answer this question in minutes.

Create a table to store the data

CREATE TABLE TABLE_SIZE
(
  ID          INTEGER GENERATED ALWAYS AS IDENTITY
(START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 9999999999999999999999999999 CACHE 20 CYCLE ORDER)
NOT NULL,
  STAMP       DATE                              DEFAULT TRUNC(sysdate)        NOT NULL,
  OWNER       VARCHAR2(30 CHAR)                 NOT NULL,
  TABLE_NAME  VARCHAR2(30 CHAR)                 NOT NULL,
  SIZE_GB     INTEGER NOT NULL,
  ROW_COUNT   INTEGER NOT NULL
);

ALTER TABLE TABLE_SIZE ADD (
  CONSTRAINT TABLE_SIZE_PK
  PRIMARY KEY
  (ID)
  ENABLE VALIDATE);

Create a SQL-Job to gather the data on weekly basis

BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        JOB_NAME          => 'GATHER_TABLE_SIZES'
       ,JOB_TYPE          => 'PLSQL_BLOCK'
       ,JOB_ACTION        =>
            q'{BEGIN
   INSERT INTO DBM_ADMIN.TABLE_SIZE (STAMP, OWNER, TABLE_NAME, SIZE_GB, ROW_COUNT)
  SELECT A.*, B.NUM_ROWS from
(SELECT TRUNC(SYSDATE) as STAMP, OWNER, TABLE_NAME, ROUND (SUM (BYTES) / 1024 / 1024 / 1024, 2) GB
    FROM (SELECT SEGMENT_NAME TABLE_NAME, OWNER, BYTES
            FROM DBA_SEGMENTS
           WHERE SEGMENT_TYPE IN
                     ('TABLE', 'TABLE PARTITION', 'TABLE SUBPARTITION')
          UNION ALL
          SELECT I.TABLE_NAME, I.OWNER, S.BYTES
            FROM DBA_INDEXES I, DBA_SEGMENTS S
           WHERE     S.SEGMENT_NAME = I.INDEX_NAME
                 AND S.OWNER = I.OWNER
                 AND S.SEGMENT_TYPE = 'INDEX'
          UNION ALL
          SELECT L.TABLE_NAME, L.OWNER, S.BYTES
            FROM DBA_LOBS L, DBA_SEGMENTS S
           WHERE     S.SEGMENT_NAME = L.SEGMENT_NAME
                 AND S.OWNER = L.OWNER
                 AND S.SEGMENT_TYPE = 'LOBSEGMENT'
          UNION ALL
          SELECT L.TABLE_NAME, L.OWNER, S.BYTES
            FROM DBA_LOBS L, DBA_SEGMENTS S
           WHERE     S.SEGMENT_NAME = L.INDEX_NAME
                 AND S.OWNER = L.OWNER
                 AND S.SEGMENT_TYPE = 'LOBINDEX')
GROUP BY TABLE_NAME, OWNER
  HAVING SUM (BYTES) / 1024 / 1024 > 10) A
  LEFT JOIN dba_tables B on A.owner=B.owner and A.Table_name=b.table_name; END; COMMIT;}'
       ,START_DATE        => SYSDATE
       ,REPEAT_INTERVAL   => 'FREQ=WEEKLY'
       ,ENABLED           => TRUE
       ,COMMENTS          => 'Gather table size');
END;
/
Werbung

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit deinem WordPress.com-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s