Just put in a new build of NPM/NTA/SAM/NCM, so far so good. I'm working with one of our db analysts, introduced him to SAM and have his interest because looking at the default Oracle template gave him ideas we can do things he finds difficult or no do-able in Oracle Enterprise Mgr. I'm finding no joy searching the posts for a particular solution. It's about tables, not databases, and although I was a database person back in the DOS days I know very little about these areas anymore.
He wrote a script [see below] that returns either no rows or rows with a string value [a table name]. If a row, or rows, is returned he wants an alert with the returned string values [the table names] the purpose is to let him know to add more capacity to the table. The Oracle templates only deal with numerical values. I looked into creating a new template and adding the component 'Oracle User Experience Monitor' but it notes "The component monitor connects to the database, performs a query, retrieves data, and then compares the data to an expected value" which is not exactly what we're looking for.
So, how does one put in a process to accomplish this? Please, and thanks...Fred
Here's the script with his comments as a preface:
This returns a text string that lists all the table spaces that need a data file added to it.
If there is no alert, this should return 0 rows.
Any rows it does return should be the text of the alert.
select close_to_limit.tablespace_name
from (select tablespace_name, COUNT(*) num_files
from dba_data_files
group by tablespace_name) raw_count,
(select tablespace_name, count(*) num_files
from dba_data_files
where (((maxbytes - bytes)/1024)/1024)/1024 <= 10
group by tablespace_name) close_to_limit,
(select tablespace_name
from dba_tablespaces
where contents !='TEMPORARY'
minus
select tablespace_name
from dba_free_space ) out_of_space
where raw_count.num_files = close_to_limit.num_files
and raw_count.tablespace_name = close_to_limit.tablespace_name
and raw_count.tablespace_name = out_of_space.tablespace_name
