Tablespace growth and usage monitoring

We want to create a mechanism to monitor growth of our tablespaces and how much space is used in them. We will use DBA_TABLESPACE_USAGE_METRICS view and store its output in a table called HELPDESK.TABLESPACE_STATS.

If you use autoextendible datafiles, according to Oracle Note 455715.1:

Tablespace_size in DBA_TABLESPACE_USAGE_METRICS takes the maximum file size for autoextensible tablespace which corresponds to maxblocks in dba_data_files.

We create HELPDESK.TABLESPACE_STATS and its indexes:

CREATE TABLE HELPDESK.TABLESPACE_STATS
(
  TABLESPACE_NAME  VARCHAR2(30 BYTE),
  TABLESPACE_SIZE  NUMBER,
  USED_SPACE       NUMBER,
  USED_PERCENT     NUMBER,
  COLLECTION_DATE  DATE
)
TABLESPACE USERS
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          1M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;


CREATE INDEX HELPDESK.DATE_IDX ON HELPDESK.TABLESPACE_STATS
(COLLECTION_DATE)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          1M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;


CREATE INDEX HELPDESK.NAME_IDX ON HELPDESK.TABLESPACE_STATS
(TABLESPACE_NAME)
LOGGING
TABLESPACE USERS
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          1M
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;

Now, schedule a job to run the following INSERT. I choose to run it once per week.

INSERT INTO HELPDESK.TABLESPACE_STATS (TABLESPACE_NAME,
                                       USED_SPACE,
                                       TABLESPACE_SIZE,
                                       USED_PERCENT,
                                       COLLECTION_DATE)
   SELECT A.TABLESPACE_NAME,
          A.USED_SPACE * B.BLOCK_SIZE,
          A.TABLESPACE_SIZE * B.BLOCK_SIZE,
          A.USED_PERCENT,
          SYSDATE
     FROM DBA_TABLESPACE_USAGE_METRICS A, DBA_TABLESPACES B
    WHERE A.TABLESPACE_NAME = B.TABLESPACE_NAME;

Let's see a scenario, where we have collected 3 weeks of data:

  SELECT TRUNC (COLLECTION_DATE) "DATE",
         TABLESPACE_NAME,
         ROUND (TABLESPACE_SIZE / 1024 / 1024, 2) "SIZE MB",
         ROUND (USED_SPACE / 1024 / 1024, 2) "USED SPACE MB",
         ROUND (USED_PERCENT, 2) "USED %"
    FROM HELPDESK.TABLESPACE_STATS
ORDER BY 1, 2;

   

DATE	TABLESPACE_NAME	SIZE MB	USED SPACE MB	USED %
02/07/2012	IDX	30	20	66.67
02/07/2012	SYSAUX	600	418.56	69.76
02/07/2012	SYSTEM	700	362.25	51.75
02/07/2012	TBL	40	22	55
02/07/2012	TEMP	20	0	0
02/07/2012	UNDOTBS1	245	3.25	1.33
02/07/2012	USERS	5	4	80
09/07/2012	IDX	30	25	83.33
09/07/2012	SYSAUX	600	418.56	69.76
09/07/2012	SYSTEM	700	362.25	51.75
09/07/2012	TBL	45	26	57.78
09/07/2012	TEMP	20	0	0
09/07/2012	UNDOTBS1	245	3.25	1.33
09/07/2012	USERS	5	4.8	96
16/07/2012	IDX	35	28.8	82.29
16/07/2012	SYSAUX	600	418.56	69.76
16/07/2012	SYSTEM	700	362.25	51.75
16/07/2012	TBL	45	35	77.78
16/07/2012	TEMP	20	0	0
16/07/2012	UNDOTBS1	245	3.25	1.33
16/07/2012	USERS	7	5.5	78.57

If we want to see the differences between two dates:

  SELECT LAST.TABLESPACE_NAME,
         ROUND ( (LAST.TABLESPACE_SIZE - FIRST.TABLESPACE_SIZE) / 1024 / 1024,
                2)
            "SIZE GROWTH MB",
         ROUND ( (LAST.USED_SPACE - FIRST.USED_SPACE) / 1024 / 1024, 2)
            "USED SPACE GROWTH MB",
         ROUND (LAST.USED_PERCENT - FIRST.USED_PERCENT, 2) "USED % GROWTH"
    FROM (SELECT TABLESPACE_NAME,
                 TABLESPACE_SIZE,
                 USED_SPACE,
                 USED_PERCENT
            FROM HELPDESK.TABLESPACE_STATS
           WHERE TRUNC (COLLECTION_DATE) = '16-JUL-12') LAST,
         (SELECT TABLESPACE_NAME,
                 TABLESPACE_SIZE,
                 USED_SPACE,
                 USED_PERCENT
            FROM HELPDESK.TABLESPACE_STATS
           WHERE TRUNC (COLLECTION_DATE) = '02-JUL-2012') FIRST
   WHERE LAST.TABLESPACE_NAME = FIRST.TABLESPACE_NAME
ORDER BY 1;

TABLESPACE_NAME	SIZE GROWTH MB	USED SPACE GROWTH MB	USED % GROWTH
IDX	5	8.8	15.62
SYSAUX	0	0	0
SYSTEM	0	0	0
TBL	5	13	22.78
TEMP	0	0	0
UNDOTBS1	0	0	0
USERS	2	1.5	-1.43

So, for instance, in 2 weeks, for tablespace TBL we gave 5 MB extra space, but 13 MB were additionally used, thus increasing its used percent by 22,78%.