Oracle Tablespaces Sensor

Monitors space in every tablespace of an Oracle database — one instance per tablespace — reporting used, free and maximum extendable size. The percentages are measured against the size the tablespace could grow to, not against its current size.

An Oracle tablespace runs out of room when its datafiles can no longer grow, which is not the same as its current datafiles being full. A tablespace whose files are 99% used but set to autoextend has plenty of room left; one at 70% with autoextend off is much closer to trouble. This sensor measures both sizes and derives its percentages from the limit, so a threshold set on it means what an administrator expects it to mean.

Setting Up

The sensor uses the same database connection profiles as the other SQL sensors and fixes the connection type to Oracle. Driver installation, profile creation and the Credential Manager are covered in SQL Query Sensors — set the connection up there first if you have not already.

No further configuration is needed. The sensor discovers the tablespaces itself.

Required Privileges

The sensor reads the data dictionary views dba_data_files and dba_free_space. The account used for the connection needs to be able to select from both:


GRANT CREATE SESSION TO netcrunch_mon;
GRANT SELECT ON sys.dba_data_files TO netcrunch_mon;
GRANT SELECT ON sys.dba_free_space TO netcrunch_mon;

Granting SELECT_CATALOG_ROLE covers both and rather more besides; the two explicit grants are the smaller privilege and the better choice for a monitoring account.

Instances

Each tablespace is an instance of the sensor, named as Oracle names it. Counters are collected per instance, so a threshold defined once applies to every tablespace and alerts identify which one crossed it.

The list of tablespaces is read once, when the sensor first runs, and kept for as long as the sensor is loaded. A tablespace created afterwards is not picked up until the sensor starts again — reopening and saving its settings, or restarting the NetCrunch Server, does that.

Counters

All sizes are in MB.

Total Space MB: The current size of the tablespace — the sum of its datafiles as they stand today.
Free Space MB: Unallocated space inside those datafiles.
Used Space MB: Total space less free space.
Max Autoextend Space MB: The size the tablespace could reach. For a datafile with autoextend on this is its MAXBYTES; for a datafile that cannot extend it is simply its current size. The sensor adds the two together across all the tablespace's files.
% Current Usage: Used space as a percentage of Max Autoextend Space MB.
% Available Space: The remainder, 100 - % Current Usage.

Reading the Percentages

Both percentages are measured against the maximum the tablespace can grow to, not against its present size.

For an autoextensible tablespace this is the number that matters — it answers how much room is left before Oracle can no longer extend. It also means a tablespace whose current datafiles are nearly full can still report a low % Current Usage, because it still has room to grow into.

Where autoextend is off, the maximum and the current size are the same, and % Current Usage is the familiar used-over-total figure.

If you want an alert on the current files filling up rather than on the hard ceiling, compare Used Space MB against Total Space MB with a calculated counter instead.

Sensor Status

OK: The query succeeded. The status message gives the number of tablespaces found.
Warning: The query failed. The message carries the error Oracle returned — usually a privilege problem, a dropped connection, or a timeout.
Error: The tablespace list could not be read at all, reported as No table spaces found.

The query is given 15 seconds by default. Where a busy database needs longer, raise the timeout in the sensor's options.

autoextendcapacitydatabasedba_data_filesdba_free_spaceoraclesensorsqlstoragetablespacetablespaces