Building an alert Script

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.