Tablespace growth and usage monitoring

Tablespace growth and usage monitoring by EasyHA

I want to create a mechanism to monitor growth of our tablespaces and how much space is used in them. 

Step by Step Guide

1.       Provided you have install the free version of EasyHA, before you monitor your databases and business data (like tablespace usage), you should please create JDBC database connection to your Oracle database.

To create JDBC connection, please click “Setup” menu, and then click “Databases Overview” button. In the “Jdbcs” page, please click “Add Jdbc” button.

The “New Jdbc” form appears, it is very easy to fill in the form. You can also click “Help” link if you don’t know how to fill in URL box.

Now I have created a connection named gnbsprd-ozq. The settings of the connection is listed below:

2.       Create item that will monitor your tablespace usage.

a.       Click “Items” menu.

b.       Now you will see the list of items.

c.        Click “Add Item” button in right upper corner.

d.       Select “Biz Data Monitor” from item type list.

e.       Select DB and fill in the SQL statement.

f.        Fill other fields like interval and description of field #1.

g.       Submit the form and review the item. The item looks like this:

The SQL statement is very critical.  This is sample SQL.

SELECT 

　　D.TOT_GROOTTE_MB "SIZE MB",

　　D.TOT_GROOTTE_MB - F.TOTAL_BYTES "USED SPACE MB"

　　FROM (SELECT TABLESPACE_NAME,

　　ROUND(SUM(BYTES) / (1024 * 1024), 2) TOTAL_BYTES,

　　ROUND(MAX(BYTES) / (1024 * 1024), 2) MAX_BYTES

　　FROM SYS.DBA_FREE_SPACE

　　GROUP BY TABLESPACE_NAME) F,

　　(SELECT DD.TABLESPACE_NAME,

　　 ROUND(SUM(DD.BYTES) / (1024 * 1024), 2) TOT_GROOTTE_MB

　　FROM SYS.DBA_DATA_FILES DD

    WHERE dd.TABLESPACE_NAME='TABLESPACE1'

　　GROUP BY DD.TABLESPACE_NAME) D

　　WHERE D.TABLESPACE_NAME = F.TABLESPACE_NAME

ORDER BY 1

If you want to monitor specified table, for example named “emp”, the sql like:

SELECT SUM(bytes)/1024/1024 Mbytese

  FROM dba_segments

 WHERE tablespace_name ='TABLESPACE1'

   AND SEGMENT_NAME='EMP'

    ORDER BY Mbytese DESC

3.       View the tablespace growth a few days later.

a.       Click “View” manu.

b.       Click “Graph View” button.

c.        Select item, and fill in date range, and click submit button.

The graph looks like: