ORACLE DATABASE ADMINISTRATOR
Basic Oracle space monitoring scripts
James Giordano
05.07.2003
Rating: -4.26- (out of 5)
|
In order to keep your Oracle databases running 24x7, you're going to need some basic monitoring scripts. Here are a few that I wrote for the HP platform.
There are two important parts of monitoring when it comes to Oracle databases: tablespaces and the HP-UX filesystems. If your shop isn't ready yet to make the investment some third-party software, you can create your own scripts in a couple hours.
When monitoring the database, the most important piece of information is the amount of free space in your tablespaces. So, here is some SQL that displays total, free, and used space in all tablespaces in your database:
This will flag the tablespaces that have less then 90% free space in them (with an asterisk in the last column).
Here is another useful SQL script that will tell you the minimum usable extents in a tablespace. This will identify when a table or index won't be able to make another extent in a tablespace. You don't want this number to reach zero -- that will cause errors in your application.
The output from both of these scripts can easily be parsed using a shell script, and scheduled using cron, in order to automate the monitoring of the Oracle database.
The last part of monitoring is at the filesystem level, most importantly, the archive log destination. Here is a simple script that monitors a filesystem called /arch and sends me an email when it reaches 90%:
By using these three scripts, you should be able to prevent most space problems that affect Oracle databases.
Reader Feedback
Steven T. writes: The first script is another incarnation of the TS Pctfree report. I like
the second script. If it were modified to use DBA_...
To continue reading for free, register below or login
To read more you must become a member of SearchOracle.com
SEGMENTS instead of outer
joining to DBA_TABLES and DBA_INDEXES, it would run more quickly, and would
report tablespaces with rollback segments as well.
Jery K. writes: I keyed in the script and it ran. This is a very useful script to run automatically to monitor tablespaces.
About the Author
James Giordano is an Oracle database administrator. He has been working with Oracle for about seven years, and also has experience with UNIX and PeopleSoft/Oracle financials.
For More Information
- Feedback: E-mail the editor with your thoughts about this tip.
- More tips: Hundreds of free Oracle tips and scripts.
- Tip contest: Have an Oracle tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize -- submit your tip today!
- Ask the Experts: Our SQL, database design, Oracle, SQL Server, DB2, metadata, and data warehousing gurus are waiting to answer your toughest questions.
- Forums: Ask your technical Oracle questions--or help out your peers by answering them--in our active forums.
- Best Web Links: Oracle tips, tutorials, and scripts from around the Web.
|
|
|
|
|
|
