I want to create a mechanism to monitor growth of our oracle tablespaces and how much space is used in them.
Step by Step Guide
1. Configure JDBC connections.
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.
- Click “Items” menu.
- Now you will see the list of items.
- Click “Add Item” button in right upper corner.
- Select “Biz Data Monitor” from item type list.
- Select DB and fill in the SQL statement.
- Fill other fields like interval and description of field #1.
- 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.
- Click “View” manu.
- Click “Graph View” button.
- Select item, and fill in date range, and click submit button.
The graph looks like: