Advantage of using Oracle Alert monitor

DROP TABLE oracheck_fs_temp;

CREATE TABLE oracheck_fs_temp (
    tablespace_name,
    total_bytes,
    free_bytes,
    max_chunk
) AS
SELECT tablespace_name, NVL(SUM(bytes), 1), 1, 1
FROM dba_data_files
GROUP BY tablespace_name;

UPDATE oracheck_fs_temp a
SET a.free_bytes = (SELECT NVL(SUM(b.bytes), 1)
                    FROM dba_free_space b
                    WHERE b.tablespace_name =
                          a.tablespace_name);
COMMIT;

UPDATE oracheck_fs_temp a
SET a.max_chunk = (SELECT NVL(MAX(b.bytes), 1)
                   FROM dba_free_space b
                   WHERE b.tablespace_name =
                         a.tablespace_name);

REM
*********************************************************
REM Tablespaces more than 95% full.
REM
*********************************************************

SELECT 
    tablespace_name ||
    ' is ' ||
    TO_CHAR(ROUND(100 - (free_bytes * 100 / total_bytes), 2)) ||
    '% full. '
FROM oracheck_fs_temp
WHERE 95 < (100 - free_bytes * 100 / total_bytes)
AND tablespace_name NOT IN ('TEMP', 'RBS', 'ROLLBACK')
ORDER BY tablespace_name;

Explanation:

1. Temporary Table Creation:
   • The script creates a temporary table oracheck_fs_temp to store information about tablespaces, including their name, total size, free space, and the largest free chunk.
2. Updating Free Space and Max Chunk:
   • The first UPDATE statement calculates the total free space in each tablespace.
   • The second UPDATE determines the largest free contiguous chunk in each tablespace.
3. Tablespace Fullness Check:
   • The final SELECT identifies tablespaces that are more than 95% full, excluding the TEMP, RBS, and ROLLBACK tablespaces.

This script is commonly used for monitoring and reporting tablespace utilization in an Oracle database. Let me know if you'd like further analysis or modification of this script.

This script has several useful features.

Features	Application
The parameters can be changed.	You can choose to generate a tablespace-full alert when the tablespace becomes a 90% full, or when it is 95% full.
If there are no alerts, the SQL prodiuces no output	You can execute this task hourly within a crontab and spool the output to a flat file. The script checks the file for lines of content. If the file is not empty, the Unix mail facility sends the alert.

Using an alert script, the DBA can monitor thousands of Oracle databases, and every alert will be sent to the DBA via email.
Let us examine how we build an alert script in the simulation below:

1. When building an alert script, we must first create a temporary table.
   Type CREATE TABLE oracheck_fs_temp to create a temporary table and hit Enter. The rest of the code containing the tablespace name and the total bytes will be completed for you.
2. The next step in building our alert log is to update the free_bytes column of our table by computing the number of free_bytes in the tablespace. You will see that we are updating our temporary table. <p>Type SET a.free_bytes, and hit Enter. The rest of the code will be completed for you. alt text (for Non-Java sidebar)
3. Next we update the max_chunk column by computing the largest piece of free space in the tablespace. Type (SELECT NVL(MAX(b.bytes), 1) and hit Enter. We now have everything that we need to compute our alerts.
4. Next, we display a list of all tablespaces that are more than 95% full. Enter the following text WHERE 95 < 100-(free_bytes*100/total_bytes) and hit Enter.
5. Above, we can easily display tables and indexes that do not have room to take another extent. This alert gives the DBA time to add to the tablespace or shrink the next extent size before the object fails to extend.
6. Next we display any object that is more than 600 extents. Of course, many DBAs use the MAXEXTENTS UNLIMITED clause to ensure that the table never locks-up with a CANNOT EXTEND failure. However, our alert is still useful, because a good DBA always sets the INITIAL extent for a table to hold the normal usage of the table, and we want to be alerted if any table or index is growing at an unexpected rate. This completes the Simulation. Click the Exit button.

Now that we see how the script functions, let us examine a mechanism to send the output, if any, to the DBA. As we have noted, if there are no alerts, the SQL produces no output. sThe following series of images explains how this script functions.

# If error messages exist (file contains 2 or more lines),
# then go on...

if [ `cat /tmp/oracheck.ora | wc -l` -ge 2 ]

#***************************************************************
# If error messages exist (file contains 2 or more lines),
# then go on...
#***************************************************************
if [ `cat /tmp/oracheck.ora | wc -l` -ge 2 ]
then
    #***********************************************************
    # Display a message on the operations console
    #***********************************************************
    console_alert=`echo "NON-EMERGENCY ORACLE ALERT.
    Contact the DBA and report this error ===>" \`cat /tmp/oracheck.ora\``
fi

#***************************************************************
# If error messages exist (file contains 2 or more lines),
# then go on...
#***************************************************************
if [ `cat /tmp/oracheck.ora | wc -l` -ge 2 ]
then
    #***********************************************************
    # Display a message on the operations console
    #***********************************************************
    console_alert=`echo "NON-EMERGENCY ORACLE ALERT.
    Contact the DBA and report this error ===>" \`cat /tmp/oracheck.ora\``

    #***********************************************************
    # Send an email with the error details
    #***********************************************************
    cat /tmp/oracheck.ora | mail -s "$ORACLE_SID alert detected" $dba_list
fi

This feature allows the DBA to drill down into important Oracle performance areas including

1. instance efficiency,
2. SQL response time,
3. SGA pool wastage, and
4. wait bottlenecks.

There is more to the data collection than instance-wide metrics and OEM can be customized to send alerts for whatever combination of metric values desired.

1. In this example, we have spooled the output from our script to /tmp/oracheck.ora.
2. Smtp Alert: If errors exist, then we invoke a UNIX script called console_alert to send an on-screen message to our operators so they know about the error.
3. Email Alert: We send the DBA an alert email. Note that we use the UNIX 'cat' command to list the file and we pipe the file as input to the mail command.

If you have any questions or comments regarding the alert script functions, then click on the Search button at the top of the screen. The next lesson wraps up this module.