RE: Unix Max Extent Script

Smith, Ron L. Thu, 16 Jan 2003 13:13:47 -0800

Title: Message

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
fi

# 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. Check nextext_$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
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
                    )
union
select /*+ RULE */
owner, tablespace_name, table_name, 2 ord_col, index_name, next_extent/1024 next_extent
from all_indexes ai
where owner like upper('%')
and next_extent > ( select max(a.bytes) largest
                      from dba_free_space a
                      where a.tablespace_name = ai.tablespace_name
                     )
order by 1,2
/
spool off
exit

That's it! Works Great!

R.Smith

Kerr-McGee Corp

-----Original Message-----
From: Jamadagni, Rajendra [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 16, 2003 2:15 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Unix Max Extent Script

search for "smenu" in Google ... it is a bunch of scripts with all sh interface.

Raj
______________________________________________________
Rajendra Jamadagni              MIS, ESPN Inc.
Rajendra dot Jamadagni at ESPN dot com
Any opinion expressed here is personal and doesn't reflect that of ESPN Inc.
QOTD: Any clod can have facts, but having an opinion is an art!

-----Original Message-----
From: Paulo Gomes [mailto:[EMAIL PROTECTED]]
Sent: Thursday, January 16, 2003 3:01 PM
To: Multiple recipients of list ORACLE-L
Subject: RE: Unix Max Extent Script

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
--
Author: Erik Williams
INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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
--
Author: Paulo Gomes
INET: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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.

RE: Unix Max Extent Script

Reply via email to