Config Info
Name Oracle Tablespaces Summary Monitor
Version 1.0

About

This monitor is a Data Adapter based monitor that retrieves information about the tablespaces (Name, Size, %Usage ...) and evaluate their statuses according to the Health Conditions. No element messages are sent. Instead, the summary message lists the tablespaces in each status (Critical / Warning)

How to use it

  1. Import the monitor
  2. Multiple Copy it to update the HOSTNAME and DBNAME (name of the ODBC) and set the user and password. Be sure that your DSN ODBC connection works properly
  3. Edit the query if necessary to ignore some tablespaces

Configuration

The following parts can be configured :

Tablespaces to ignore
You can ignore tablespaces directly in the Data Adapter's query WHERE clause : >where tablespace_name not in ('A_TABLESPACE','ANOTHER_TABLESPACE')

Messages sent

The monitor sends messages that look like :

HOSTNAME - DBNAME : the usage of some tablespaces is critically high -Tablespaces in a Critical status : SYSTEM_TB (95%), TOTO (92%) -Tablespaces in a Warning status : OTHER (85%) 

To filter, the text ORACLE-TABLESPACES is sent as VAR04

 

Monitor - HOSTNAME - DBNAME - Tablespaces

General

Property Value
ClassID ODBCAgent.1
CreateEventWhenNoData true
Description HOSTNAME - DBNAME - Tablespaces
GenerateHealthEvent 2
Name HOSTNAME - DBNAME - Tablespaces
RedirectHealthEvent 5

 

Script - Set Health Wizard

Additional Parameters

Name Type Value
TableSpace_Name String  
TableSpace_SizeMb Numeric 0
TableSpace_FreeSpaceMb Numeric 0
TableSpace_MaxMb Numeric 0
TableSpace_PercentUsage Numeric 0

 

Pre-Group

#Start with empty list to be filled depending on criticity Tablespaces_Critical = [] Tablespaces_Warning = [] 

 

Pre-Health Check

TableSpace_Name = RecordFieldValue01 TableSpace_SizeMb = int(RecordFieldValue02) TableSpace_FreeSpaceMb = int(RecordFieldValue03) TableSpace_MaxMb = int(RecordFieldValue04) TableSpace_PercentUsage = int(RecordFieldValue05) 

 

Health Rules

Criticity Code
Critical NumberOfRows >0 and TableSpace_PercentUsage > 90
Warning NumberOfRows >0 and TableSpace_PercentUsage > 80
Minor NumberOfRows == 0
Success True

 

Templates

Criticity Code
Critical &Host - &DBName : the usage of some tablespaces is critically high

-Tablespaces in a Critical status : &TableSpace_Critical_txt -Tablespaces in a Warning status : &TableSpace_Warning_txt

Warning &Host - &DBName : the usage of some tablespaces is very high

-Tablespaces in a Warning status : &TableSpace_Warning_txt

Minor &Host - &DBName : no tablespace information was retrieved
Success &Host - &DBName : the monitored tablespaces are in a correct status

 

Sent Variables

ID Variable
VAR02 Host
VAR03 DBName
VAR04 ORACLE-TABLESPACES

 

Post-Health

if VSMCurrentElementHealth == CRITICAL : Tablespaces_Critical.append( TableSpace_Name + '(' + str( TableSpace_PercentUsage ) + ' %)') if VSMCurrentElementHealth == WARNING : Tablespaces_Warning.append( TableSpace_Name + '(' + str( TableSpace_PercentUsage ) + ' %)') 

 

Post-Group

#generate tje text list of tablespaces for the final message TableSpace_Critical_txt = ", ".join(Tablespaces_Critical) TableSpace_Warning_txt = ", ".join(Tablespaces_Warning) 

 

Datasource - ODBC DataSource

General

Property Value
ClassID ODBCAgent.1
Description  
ErrorRetryTime 60
IntervalRetries 10
Kind 0
Name ODBC DataSource
Retries 1
SharingKind 1
Timer 7200

 

Implementation

Property Value
DBName DBNAME
Host HOSTNAME
NumberOfRows 50
PassEncryption LEVEL1
Password 70C043874CC03138
Query select

tablespace_name as "Tablespace"

,round(sum(lalloc)/1024/1024) as "Size Mb"

,round(sum(lfree)/1024/1024) as "Free Mb"

,round(max(lfree)/1024/1024) as "Max Mb"

,round((sum(lalloc)-sum(lfree))/sum(lalloc)*100,0) as "Percent Usage"

from

(select tablespace_name

,bytes lalloc

,0 lfree

from dba_data_files

union all

select tablespace_name

, 0 lalloc

,bytes lfree

from dba_free_space

)

 

where tablespace_name not in ('A_TABLESPACE','ANOTHER_TABLESPACE')

 

group by tablespace_name

 

order by 5

Reconnect false
SecureMode false
User user

 

See also

 


Still have questions? We can help. Submit a case to Technical Support.

Last Modified On: October 18, 2018