Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Unix Max Extent Script
Here is what we run. It is part of a group of scripts we run every 15
minutes that monitor all the databases. The script will either send a page
or an email, depending on how it is called.
The command to run the script (cron every 15 minutes): nextext.sh prod zrls1 > /dev/null 2>&1
The nextext.sh Unix script:
#! /bin/sh -x
# DBA MONITORING SCRIPTS
# ******************************************************************
#
# Author: Ron Smith
# Date: 06/18/98
# Funtion: Checks for objects that cannot allocate next
# extent.
#
# ******************************************************************
#
# CHANGE HISTORY
#
# DATE WHO Reason for Change
# 06/18/98 Ron Smith New Prog
#
# ******************************************************************
#
# FUNCTION
#
# This script calls nextext.sql.
# The function of this script is to report database objects that
# cannot allocate the next extent in the tablespace.
# If an object is found that cannot be extended, an error file
# is created and a page is sent to the DBA.
#
# If an error file already exists, the script exits without any
# action. The DBA should delete the error file when the problem
# is resolved. Another script should be scheduled to run daily
# to delete the error file so the DBA is paged at least once a
# day if the condition continues.
#
# If the id of the DBA is a Zid, a page will be sent. If the
# id of the DBA is an email address (determined by looking for
# an "@" ) , an EMAIL will be sent.
#
# ******************************************************************
#
# PREREQUISITES
#
# The OPS$ORACLE user must exist in the instance. This can be
# created by running the opsuer.sql script in SQLPLUS while
# logged on as SYSTEM.
#
# The cdmonitoring script must exist in the home/oracle
# directory.
#
# ******************************************************************
#
# RUN SYNTAX
#
# nextext.sh (sid) (oncall dba)
#
#
# ******************************************************************
# cd to the monitoring script directory
. $HOME/cdmonitoring.sh
ORACLE_SID=$1
export ORACLE_SID
DBA=$2
export DBA
ATCNT=`echo $DBA | grep @ | wc -l`
export ATCNT
EMAIL=$3
export EMAIL
ORACLE_HOME=`grep "^$ORACLE_SID:" /etc/oratab | head -1 | cut -d: -f2`
export ORACLE_HOME
PATH=$ORACLE_HOME/bin:/usr/local/bin:$PATH:.
export PATH
# Delete the old list file if it exists
if [ -f nextext_$ORACLE_SID.lst ]
then rm nextext_$ORACLE_SID.lst
fi
# Check to see if an error file exists. If it does get out.
if [ -f nextext_$ORACLE_SID.err ]
then echo 'Error file nextext_'$ORACLE_SID'.err exists - will exit now'
exit
fi
# If sending to EMAIL address, run sql with headings on
if [ "$ATCNT" -gt "0" ]
then sqlplus / @nextext.sql on else sqlplus / @nextext.sql off
# If there is anything in the lst file then send a message
if [ -s nextext_$ORACLE_SID.lst ]
then echo "-DBA- Cannot Alloc Next Ext " > nextext_$ORACLE_SID.err echo "SID=" $ORACLE_SID " " >> nextext_$ORACLE_SID.err cat nextext_$ORACLE_SID.lst >> nextext_$ORACLE_SID.err if [ "$ATCNT" -gt "0" ] then echo "email sent" elm -s "-DBA- Warning! $ORACLE_SID Next Extent Warning" $DBA < nextext_$ORACLE_SID.err else LC=`cat nextext_$ORACLE_SID.lst | sed -e 's/ */ /g' | wc -c` echo $LC if [ "$LC" -gt "160" ] then echo "Too many errors to send. Checknextext_$ORACLE_SID.lst" >> nextext_$ORACL E_SID.err
else cat nextext_$ORACLE_SID.lst >> nextext_$ORACLE_SID.err fi echo "page sent" pager $DBA "`cat nextext_$ORACLE_SID.err`" fi
The nextext.sql script:
set linesize 80
set feedback off
set verify on
set heading &1
column owner format a10
column tablespace_name format a15
column table_name format a15
column index_name format a15
column next_extent format 999,999,990
column ord_col noprint
spool nextext_$ORACLE_SID.lst
select /*+ RULE */
owner, tablespace_name, table_name, 1 ord_col,'' index_name,
next_extent/1024 next_extent
from all_tables at
where owner like upper('%')
and next_extent > (select max(a.bytes) largest
from dba_free_space a where a.tablespace_name = at.tablespace_name )
from dba_free_space a where a.tablespace_name = ai.tablespace_name )
That's it! Works Great!
R.Smith
Kerr-McGee Corp
-----Original Message-----
Sent: Thursday, January 16, 2003 2:15 PM
To: Multiple recipients of list ORACLE-L
search for "smenu" in Google ... it is a bunch of scripts with all sh interface.
Raj
QOTD: Any clod can have facts, but having an opinion is an art!
-----Original Message-----
Sent: Thursday, January 16, 2003 3:01 PM
To: Multiple recipients of list ORACLE-L
maybe u should use orasnap from a windows client???
bye
Paulo
-----Original Message-----
Sent: quinta-feira, 16 de Janeiro de 2003 18:36
To: Multiple recipients of list ORACLE-L
I have found many great SQL scripts to identify segments whose next extents will not fit into their tablespace and segments whose number of extents are approaching the max number of extents. But, what I am looking for is a Unix shell script that will run one of these scripts and mail alerts based on the
results. Has anyone written a shell script that will do this that they would
like to share? There are many other SQL scripts that I would like to run from cron to evaluate SQL script results.
Thanks
Erik
-- Please see the official ORACLE-L FAQ: http://www.orafaq.net <http://www.orafaq.net> -- Author: Erik Williams INET: ewilliams_at_brownco.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com <http://www.fatcity.com> San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.net <http://www.orafaq.net> -- Author: Paulo Gomes INET: PGomes_at_Datinfor.pt Fat City Network Services -- 858-538-5051 http://www.fatcity.com <http://www.fatcity.com> San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). If you are not the intended recipient of this e-mail message, any use, distribution or copying of the message is prohibited. Please let me know immediately by return e-mail if you have received this message by mistake, then delete the e-mail message. Thank you. -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Smith, Ron L. INET: rlsmith_at_kmg.com Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).Received on Thu Jan 16 2003 - 14:40:09 CST